package com.o2o.regularservice.brand_modular_202004.utils

import com.alibaba.fastjson.JSON
import org.apache.spark.sql.{DataFrame, SparkSession}

/**
  * @Description TODO 
  * @Author liutaowei
  * @Date 2019-5-4 23:28
  */
class brand_modify_util {

  /**
    * @ describe 更改新增品牌
    * @return
    *  data---》新增品牌数据
    */


  def brand_modify_1(new_brand:DataFrame,spark:SparkSession,platformName:String)={
//    spark.sqlContext.udf.register("replace", replace _)

    // 新增数据
    val datas = new_brand
      .where("brandName != '-1' and brandValueId != '-1' ")
      .selectExpr("brandCcId","brandName","brandValueId","firstCategoryId"
            ,"'-1' brand_state","'-1' brand_type","'0' brand_isLaoZiHao"
          ,"upper(brandName) brandName_cn","upper(brandName) brandName_en",s"'${platformName}' platform","timeStamp")



    //对数据中的brand_cn 进行处理
    val res = brand_hmxs(datas,spark)



    // 处理新增中分类为 10019的品牌
//    ,"brandName_en"
    val brand_19 = brand_mod_19(res,spark)
      .select("brandCcId","brandName","brandName_cn","brandValueId","brand_isLaoZiHao","brand_state","brand_type","firstCategoryId","platform","timeStamp")



    // 处理分类为 10028、10020等分类的数据
    val brand_case_1 = brand_mod_case1(res,spark)
      .select("brandCcId","brandName","brandName_cn","brandValueId","brand_isLaoZiHao","brand_state","brand_type","firstCategoryId","platform","timeStamp")


    // 有处理的数据的合并
    val resultTable = brand_19.union(brand_case_1)

    resultTable.cache()


    val frame_1 = special_name(resultTable,spark)


    frame_1
  }

  /**
    * 一、将新增品牌表通过正则处理品牌名称
    */
  private def brand_hmxs(datas:DataFrame,spark:SparkSession) ={

    val spTable = datas.toJSON.rdd.map(line => {
      val jsons = JSON.parseObject(line)
      val brandName = jsons.getString("brandName")
        .replaceAll("“", "")
        .replaceAll("”", "")
        .replaceAll("【", "")
        .replaceAll("】", "")
        .replaceAll("\\[", "")
        .replaceAll("\\]", "")
        .replaceAll("&", "&")
        .replaceAll("（", "(")
        .replaceAll("）", ")")
        .replaceAll("＆", "&")
        .replaceAll("、", "")
        .replaceAll("#", "")
        .replaceAll(";","")
        .replaceAll("；","")
        .replaceAll("男鞋","")
        .replaceAll("女鞋","")
        .replaceAll("童鞋","")
        .replaceAll("男包","")
        .replaceAll("女包","")
        .replaceAll("单肩包","")
        .replaceAll("食品","")
        .replaceAll("罐头","")
        .replaceAll("酸奶","")
        .replaceAll("牛奶","")
        .toUpperCase()

      //    只有中文
      if (brandName.replaceAll("""[0-9\u4e00-\u9fa5 ]+""", "").length == 0) {
        //        println("只有中文：" + brandName)
        jsons.put("brandName_cn",brandName)
        //    只有英文
      } else if (brandName.replaceAll("""[0-9A-Z&-' ]+""", "").length == 0) {
        //        println("只有英文：" + brandName)
        jsons.put("brandName_cn",brandName)
        //   只有中文和英文
      }else if(brandName.replaceAll("""[0-9\u4e00-\u9fa5 ]+""", "")
        .replaceAll("""[0-9A-Z&-' ]+""", "").length == 0){
              val cn = brandName.replaceAll("""[0-9A-Z&-']+""", "").replaceAll("/","").trim
              val en = brandName.replaceAll("""[\u4e00-\u9fa5]+""", "").replaceAll("/","").trim
              if(cn.length ==0){
                //          println("只有中文和英文：英文："+en)
                jsons.put("brandName_cn",en)
              }else if(en.length == 0){
                //          println("只有中文和英文：中文："+cn)
                jsons.put("brandName_cn",cn)
              }else{
                //          println("只有中文和英文：中文+英文："+cn +"/"+en)
                jsons.put("brandName_cn",cn +"/"+en)
              }
        //    只有中文和英文并且带斜杠
      }else if(brandName.replaceAll("""[0-9\u4e00-\u9fa5 ]+""", "")
        .replaceAll("""[0-9A-Z&-' ]+""", "")
        .replaceAll("/","")
        .length == 0){
              val cn = brandName.replaceAll("""[0-9A-Z&-']+""", "").replaceAll("/","").trim
              val en = brandName.replaceAll("""[\u4e00-\u9fa5]+""", "").replaceAll("/","").trim
              if(cn.length == 0){
                //          println("只有中文和英文并且带斜杠：英文："+en)
                jsons.put("brandName_cn",en)
              }else if(en.length == 0){
                //          println("只有中文和英文并且带斜杠：中文："+cn)
                jsons.put("brandName_cn",cn)
              }else{
                //          println("只有中文和英文并且带斜杠：中文+英文："+cn +"/"+en)
                jsons.put("brandName_cn",cn +"/"+en)
              }
        //  只有中文和英文并且带括号
      }else if(brandName.replaceAll("""[0-9\u4e00-\u9fa5 ]+""", "")
        .replaceAll("""[0-9A-Z&-' ]+""", "")
        .replaceAll("\\(","")
        .replaceAll("\\)","")
        .length == 0){
                val cn = brandName.replaceAll("""[0-9A-Z&-']+""", "")
                  .replaceAll("/","")
                  .replaceAll("\\(","")
                  .replaceAll("\\)","")
                  .trim
                val en = brandName.replaceAll("""[\u4e00-\u9fa5]+""", "")
                  .replaceAll("/","")
                  .replaceAll("\\(","")
                  .replaceAll("\\)","")
                  .trim
                //      判断数据中是否有英文
                if(en.length ==0){
                  //          println("只有中文和英文并且带括号：中文+英文："+cn)
                  jsons.put("brandName_cn",cn)
                }else if(cn.length == 0){
                  //          println("只有中文和英文并且带括号：中文+英文："+en)
                  jsons.put("brandName_cn",en)
                } else{
                  //          println("只有中文和英文并且带括号：中文+英文："+cn+"/"+en)
                  jsons.put("brandName_cn",cn+"/"+en)
                }
        //      这个是中文+英文+斜杠+括号
      }else if(brandName.replaceAll("""[0-9\u4e00-\u9fa5 ]+""", "")
        .replaceAll("""[0-9A-Z&-' ]+""", "")
        .replaceAll("/","")
        .replaceAll("\\(","")
        .replaceAll("\\)","").length == 0){

                val cn = brandName.replaceAll("""[0-9A-Z&-']+""", "")
                  .replaceAll("/","")
                  .replaceAll("\\(","")
                  .replaceAll("\\)","")
                  .trim
                val en = brandName.replaceAll("""[\u4e00-\u9fa5]+""", "")
                  .replaceAll("/","")
                  .replaceAll("\\(","")
                  .replaceAll("\\)","")
                if(cn.length ==0){
                  //          println("中文+英文+斜杠+括号：英文:",en)
                  jsons.put("brandName_cn",en)
                }else if(en.length == 0){
                  //          println("中文+英文+斜杠+括号：中文：",en)
                  jsons.put("brandName_cn",cn)
                }else{
                  //          println("中文+英文+斜杠+括号：中文+英文：",cn+"/"+en)
                  jsons.put("brandName_cn",cn+"/"+en)
                }
      }else{
              val cn = brandName.replaceAll("""[0-9A-Z&-'][-.]+""", "")
                .replaceAll("/","")
                .replaceAll("\\(","")
                .replaceAll("\\)","")
                .trim()
              val en = brandName.replaceAll("""[\u4e00-\u9fa5]+""", "")
                .replaceAll("/","")
                .replaceAll("\\(","")
                .replaceAll("\\)","")
                .trim
              if(cn.length ==0){
                jsons.put("brandName_cn",en)
              }else if(en.length == 0){
                jsons.put("brandName_cn",cn)
              }else if(cn.equals("-1")||cn.equals("·")||cn.equals("-")||cn.equals("+")||cn.equals(".")||cn.equals("の")){
                //          println("只有中文和英文：中文是-1："+"其他/OTHER")
                jsons.put("brandName_cn","其他/OTHER")
              }else if(en.equals("-1")||en.equals("·")||en.equals("-")||en.equals("+")||en.equals(".")||en.equals("の")){
                //          println("只有中文和英文：英文是-1："+"其他/OTHER")
                jsons.put("brandName_cn","其他/OTHER")
              }else if(cn.contains("+") || en.contains("+") || cn.contains("-")||
                en.contains("-")||cn.contains(".")||en.contains(".")||cn.contains("·")||en.contains("·")||
                cn.contains("の")||en.contains("の")){
                //          println("中文带+：",cn)
                jsons.put("brandName_cn",brandName)
              }else{
                //                    println("只有中文和英文：中文+英文："+cn +"/"+en)
                jsons.put("brandName_cn",cn +"/"+en)
              }
      }





      jsons.toString
    })


    val resultAA = spark.sqlContext.read.json(spTable)
      .drop("brandName_en")

    resultAA
  }


  /**
    * 二、将正则处理过后的品牌表中提取10019分类数据，通过case when修改品牌名称
    * 处理新增中分类为 10019的品牌
    */
  private def brand_mod_19(datas:DataFrame,spark:SparkSession)={
    val fir_19 = datas.where("firstCategoryId = '10019'")
      .registerTempTable("first_19")

//    处理10019分类
    val case1 = spark.sqlContext.sql(
      """
        |select *, case
        |when firstCategoryId = '10019' and brandName_cn rlike '资生堂臻白无瑕' then '资生堂/Shiseido'
        |when firstCategoryId = '10019' and (brandName_cn = '优理氏' or brandName_cn = 'UNES') then '优理氏/UNES'
        |when firstCategoryId = '10019' and (brandName_cn = '植贝' or brandName_cn = 'VSHEIL') then '植贝/VSHEIL'
        |when firstCategoryId = '10019' and (brandName_cn = '韩伦美羽' or brandName_cn = 'HANUMU') then '韩伦美羽/HANUMU'
        |when firstCategoryId = '10019' and (brandName_cn = '梦妆' or brandName_cn = 'MAMONDE') then '梦妆/MAMONDE'
        |when firstCategoryId = '10019' and (brandName_cn = '妆度' or brandName_cn = 'ZOONDUOO') then '妆度/ZOONDUOO'
        |when firstCategoryId = '10019' and (brandName_cn = 'ZEESEA' or brandName_cn = '姿色') then '姿色/ZEESEA'
        |when firstCategoryId = '10019' and (brandName_cn = '珂拉琪' or brandName_cn = 'COLOR KEY') then '珂拉琪/COLOR KEY'
        |when firstCategoryId = '10019' and (brandName_cn = '缤迪' or brandName_cn = 'NOTO') then '缤迪/NOTO'
        |when firstCategoryId = '10019' and (brandName_cn = '辰木' or brandName_cn = 'SAPLING') then '辰木/SAPLING'
        |when firstCategoryId = '10019' and (brandName_cn = '泊舒' or brandName_cn = 'POOSOS') then '泊舒/POOSOS'
        |when firstCategoryId = '10019' and (brandName_cn = '雪阁' or brandName_cn = 'SULGEE') then '雪阁/SULGEE'
        |when firstCategoryId = '10019' and brandName_cn = '澳芝曼/G&M' then '澳芝曼/GM'
        |when firstCategoryId = '10019' and (brandName_cn rlike '安肌心语' or brandName_cn rlike '敏感话题' or brandName_cn rlike 'D PROGRAM' or brandName_cn like "安肌心语/DPROGRAM") then '安肌心语/d program'
        |when firstCategoryId = '10019' and (brandName_cn = '李施德林' or brandName_cn = 'LISTERINE')  then "李施德林/LISTERINE"
        |when firstCategoryId = '10019' and (brandName_cn = '李医生' or brandName_cn = 'DOCTORLI' or brandName_cn = 'DOCTOR LI') then '李医生/DOCTOR LI'
        |when firstCategoryId = '10019' and (brandName_cn = '京润珍珠' or brandName_cn = "GNPEARL") then '京润珍珠宝贝/GNPEARL'
        |when firstCategoryId = '10019' and  brandName_cn = '安娜贝拉' then 'ANNABELLA'
        |when firstCategoryId = '10019' and (brandName_cn = '葵儿' or brandName_cn = 'CUIR')  then '葵儿/CUIR'
        |when firstCategoryId = '10019' and (brandName_cn = 'VENUSMARBLE')  then 'VENUS MARBLE'
        |when firstCategoryId = '10019' and (brandName_cn = 'LQUALYN' or brandName_cn = '歐泉琳' or brandName_cn = '欧泉琳')  then "欧泉琳/L’QUALYN"
        |when firstCategoryId = '10019' and (brandName_cn =  '丸美' or brandName_cn = 'MARUBI') then '丸美/MARUBI'
        |when firstCategoryId = '10019' and (brandName_cn = '欧莱雅' or brandName_cn = "L'OREAL") then "欧莱雅/L'OREAL"
        |when firstCategoryId = '10019' and (brandName_cn = '芙丽芳丝' or brandName_cn = 'FREEPLUS' or brandName_cn = "芙丽芳丝/" or brandName_cn = "FREEPLUS芙丽芳丝" ) then '芙丽芳丝/FREEPLUS'
        |when firstCategoryId = '10019' and (brandName_cn = '爱尔塔' or brandName_cn = 'ELTAMD' or brandName_cn = 'ELTA MD' or brandName_cn = "/ELTA MD") then '爱尔塔/ELTA MD'
        |when firstCategoryId = '10019' and (brandName_cn = '珊珂' or brandName_cn = 'SENKA') then '珊珂/SENKA'
        |when firstCategoryId = '10019' and (brandName_cn = '妮维雅' or brandName_cn = 'NIVEA' or brandName_cn = "妮维雅/" or brandName_cn = "妮维雅男士/NIVEAMEN") then '妮维雅/NIVEA'
        |when firstCategoryId = '10019' and (brandName_cn = '悦诗风吟' or brandName_cn = 'INNISFREE') then '悦诗风吟/INNISFREE'
        |when firstCategoryId = '10019' and (brandName_cn = '曼秀雷敦' or brandName_cn = 'MENTHOLATUM' or brandName_cn = '曼秀雷敦男士'or brandName_cn = "曼秀雷敦/" or brandName_cn rlike "曼秀雷敦/MENTHOLATUM") then '曼秀雷敦/MENTHOLATUM'
        |when firstCategoryId = '10019' and (brandName_cn = '爱惠佳' or brandName_cn = 'AHC' or brandName_cn = 'A.H.C'
        |or brandName_cn = '爱惠佳/AHC' or brandName_cn = '爱和纯') then '爱和纯/A.H.C'
        |when firstCategoryId = '10019' and (brandName_cn rlike 'BEAUTYBUFFET' or brandName_cn = 'BEAUTY BUFFET'
        |or brandName_cn rlike '美丽自助' or (brandName_cn = 'BEAUTY' and brandName_cn = 'BUFFET') or brandName_cn = '天天美丽') then 'BEAUTY BUFFET'
        |when firstCategoryId = '10019' and (brandName_cn = '丝塔芙' or brandName_cn = 'CETAPHIL')  then '丝塔芙/CETAPHIL'
        |when firstCategoryId = '10019' and (brandName_cn = 'WIS' or brandName_cn = '微希(WIS)' ) then 'WIS'
        |when firstCategoryId = '10019' and (brandName_cn = 'GF' or brandName_cn = '高夫') then '高夫/GF'
        |when firstCategoryId = '10019' and (brandName_cn = '多芬' or brandName_cn = 'DOVE')  then '多芬/DOVE'
        |when firstCategoryId = '10019' and (brandName_cn = 'UNO' or brandName_cn = '吾诺') then '吾诺/UNO'
        |when firstCategoryId = '10019' and (brandName_cn = '百雀羚' or brandName_cn = 'PECHOIN') then "百雀羚/PECHOIN"
        |when firstCategoryId = '10019' and (brandName_cn = '雅诗兰黛' or brandName_cn = 'ESTEELAUDER' or brandName_cn = 'ESTEE LAUDER' or brandName_cn rlike "ESTEE LAUDER 雅诗兰黛"
        |or brandName_cn rlike "雅诗兰黛/ESTEELAUDER")  then "雅诗兰黛/ESTEE LAUDER"
        |when firstCategoryId = '10019' and (brandName_cn = '自然堂' or brandName_cn =  'CHANDO')  then '自然堂/CHANDO'
        |when firstCategoryId = '10019' and (brandName_cn = "旁氏" or brandName_cn = "POND'S" or brandName_cn rlike 'UNILEVERUSA') then "旁氏/POND'S"
        |when firstCategoryId = '10019' and (brandName_cn = "花印" or brandName_cn = "HANAJIRUSHI" or brandName_cn = "HANAJIRUSHI/花印")  then '花印/HANAJIRUSHI'
        |when firstCategoryId = '10019' and (brandName_cn = '兰蔻' or brandName_cn = 'LANCOME')  then '兰蔻/LANCOME'
        |when firstCategoryId = '10019' and (brandName_cn = '城野医生' or brandName_cn = "DR.CILABO" or brandName_cn rlike "DR.CI:LABO") then "城野医生/DR.CI:LABO"
        |when firstCategoryId = '10019' and (brandName_cn = '雅漾' or brandName_cn = 'AVENE') then '雅漾/AVENE'
        |when firstCategoryId = '10019' and (brandName_cn = '娥佩兰' or brandName_cn = 'OPERA' or brandName_cn = "娥佩兰/" or brandName_cn rlike "OPERA娥佩兰"
        |or brandName_cn rlike "NATURIE 娥佩兰") then '娥佩兰/OPERA'
        |when firstCategoryId = '10019' and (brandName_cn = '奥尔滨' or brandName_cn =  'ALBION' or brandName_cn = "ALBION奥尔滨") then '奥尔滨/ALBION'
        |when firstCategoryId = '10019' and (brandName_cn = '科颜氏' or brandName_cn = "KIEHL'S" or brandName_cn = 'KIEHLS' or brandName_cn = "科颜氏/KIEHL＇S"
        |or brandName_cn = "科颜氏/KIEHL" or brandName_cn = "科颜氏/KIEHL'S") then '科颜氏/KIEHLS'
        |when firstCategoryId = '10019' and (brandName_cn = '理肤泉' or brandName_cn = "LA ROCHE-POSAY" or brandName_cn = "理肤泉/LAROCHEPOSAY" or brandName_cn = "LA ROCHE POSAY") then '理肤泉/LA ROCHE-POSAY'
        |when firstCategoryId = '10019' and (brandName_cn = '黛珂' or brandName_cn = "COSME DECORTE" or brandName_cn = "黛珂/DECORTE" ) then '黛珂/COSME DECORTE'
        |when firstCategoryId = '10019' and (brandName_cn = '悦木之源' or brandName_cn = "ORIGINS" or brandName_cn = "悦 木之源" ) then '悦木之源/ORIGINS'
        |when firstCategoryId = '10019' and (brandName_cn = '薇诺娜' or brandName_cn = 'WINONA') then '薇诺娜/WINONA'
        |when firstCategoryId = '10019' and (brandName_cn = '茵芙莎' or brandName_cn = 'IPSA')  then '茵芙莎/IPSA'
        |when firstCategoryId = '10019' and (brandName_cn = 'EAU PRECIEUSE' or brandName_cn = '珍贵水') then 'EAU PRECIEUSE'
        |when firstCategoryId = '10019' and (brandName_cn = '欧缇丽' or brandName_cn = 'CAUDALIE')  then '欧缇丽/CAUDALIE'
        |when firstCategoryId = '10019' and (brandName_cn = '玉兰油' or brandName_cn = 'OLAY') then '玉兰油/OLAY'
        |when firstCategoryId = '10019' and (brandName_cn = '梵蜜琳' or brandName_cn = 'THANMELIN') then '梵蜜琳/THANMELIN'
        |when firstCategoryId = '10019' and (brandName_cn = '海蓝之谜' or brandName_cn = 'LAMER' or brandName_cn = "LAMER海蓝之谜" or brandName_cn = 'LA MER') then '海蓝之谜/LA MER'
        |when firstCategoryId = '10019' and (brandName_cn = '倩碧' or brandName_cn = 'CLINIQUE') then '倩碧/CLINIQUE'
        |when firstCategoryId = '10019' and (brandName_cn = '珂润' or brandName_cn = 'CUREL') then '珂润/CUREL'
        |when firstCategoryId = '10019' and (brandName_cn = '完美' or brandName_cn = 'PERFECT' or brandName_cn = '完美/PERFECT' ) then '完美/PERFECT'
        |when firstCategoryId = '10019' and (brandName_cn = '希思黎' or brandName_cn = 'SISLEY' or brandName_cn = "SISLEY 希思黎") then '希思黎/SISLEY'
        |when firstCategoryId = '10019' and (brandName_cn = '劲霸' or brandName_cn = 'RIMULA') then '劲霸/RIMULA'
        |when firstCategoryId = '10019' and (brandName_cn = '膜法世家' or brandName_cn rlike 'MASKFAMILY 1908' or brandName_cn = 'MASK FAMILY') then '膜法世家/MASK FAMILY'
        |when firstCategoryId = '10019' and (brandName_cn = '御泥坊' or brandName_cn rlike '御泥坊男士') then '御泥坊'
        |when firstCategoryId = '10019' and brandName_cn = "九叶草" then "九叶草/JIUYECAO"
        |when firstCategoryId = '10019' and (brandName_cn = '一叶子' or brandName_cn = 'ONELEAF' or brandName_cn = "一叶子/ONELEAF" or brandName_cn = "ONE LEAF") then '一叶子/ONE LEAF'
        |when firstCategoryId = '10019' and (brandName_cn = '美迪惠尔' or brandName_cn = 'MEDIHEAL' or brandName_cn = "MEDIHEAL/美迪惠尔") then '美迪惠尔/MEDIHEAL'
        |when firstCategoryId = '10019' and (brandName_cn = '春雨' or brandName_cn =  '春雨/PAPARECIPE' or brandName_cn =  "春雨（PAPA RECIPE）"
        |or brandName_cn = 'PAPA RECIPE' or brandName_cn = 'PAPARECIPE' or brandName_cn rlike 'PAPA RECIPE 春雨') then '春雨/PAPA RECIPE'
        |when firstCategoryId = '10019' and (brandName_cn = 'MG' or brandName_cn = '美即' or brandName_cn rlike '猫纸/MG') then '美即/MG'
        |when firstCategoryId = '10019' and (brandName_cn = '蒂佳婷' or brandName_cn rlike 'DR.JART+') then '蒂佳婷/DR.JART+'
        |when firstCategoryId = '10019' and (brandName_cn = '捷俊' or brandName_cn = 'JAYJUN' or brandName_cn = '水光') then '捷俊/JAYJUN'
        |when firstCategoryId = '10019' and (brandName_cn = '森田药妆' or brandName_cn =  'DR.MORITA') then '森田药妆/DR.MORITA'
        |when firstCategoryId = '10019' and (brandName_cn = '佑天兰' or brandName_cn = 'UTENA') then '佑天兰/UTENA'
        |when firstCategoryId = '10019' and (brandName_cn = '斯内普' or brandName_cn = 'SNP') then '斯内普/SNP'
        |when firstCategoryId = '10019' and (brandName_cn = '宝罗丝丹' or brandName_cn = 'BONOSIDAN') then '宝罗丝丹/BONOSIDAN'
        |when firstCategoryId = '10019' and (brandName_cn = '雪花秀' or brandName_cn = 'SULWHASOO')  then '雪花秀/SULWHASOO'
        |when firstCategoryId = '10019' and (brandName_cn = '法兰琳卡' or brandName_cn = 'FRANIC')  then '法兰琳卡/FRANIC'
        |when firstCategoryId = '10019' and (brandName_cn = '肌司研' or brandName_cn  = 'JM SOLUTION' or brandName_cn rlike 'JMSOLUTION' or brandName_cn rlike 'JMSOULTION') then '肌司研/JMSOLUTION'
        |when firstCategoryId = '10019' and (brandName_cn = 'WHOO后' or brandName_cn = '后/WHOO' or brandName_cn = 'THEHISTORYOF后/THEHISTORYOFWHOO'
        |or brandName_cn = 'THE HISTORY OF WHOO/后' or brandName_cn = '后/THE HISTORY OF WHOO' or brandName_cn = 'THE HISTORY OF/后'
        |or brandName_cn = '后/THE HISTORY OF' or brandName_cn = '后WHOO'
        |or brandName_cn = 'WHOO/后' or brandName_cn = '后 WHOO ' or brandName_cn = 'WHOO')  then '后/THEHISTORYOFWHOO'
        |when firstCategoryId = '10019' and (brandName_cn = '欧诗漫' or brandName_cn = 'OSM') then "欧诗漫/OSM"
        |when firstCategoryId = '10019' and (brandName_cn = '韩束' or brandName_cn = 'KANS') then "韩束/KANS"
        |when firstCategoryId = '10019' and (brandName_cn = '温碧泉' or brandName_cn = 'WETHERM') then "温碧泉/WETHERM"
        |when firstCategoryId = '10019' and (brandName_cn = '美人符' or brandName_cn  = 'BEAUTY SIGN') then "美人符/BEAUTY SIGN"
        |when firstCategoryId = '10019' and (brandName_cn = '平凡之美' or brandName_cn = 'THE ORDINARY') then "平凡之美/THE ORDINARY"
        |when firstCategoryId = '10019' and (brandName_cn = '安热沙' or brandName_cn = 'ANESSA')  then '安热沙/ANESSA'
        |when firstCategoryId = '10019' and (brandName_cn = '莱斯璧' or brandName_cn = 'RECIPE' or brandName_cn = "莱斯璧/" or brandName_cn = 'RE:CIPE') then '莱斯璧/RE:CIPE'
        |when firstCategoryId = '10019' and (brandName_cn = '碧柔' or brandName_cn = 'BIORE' or brandName_cn = "碧柔 BIORE")  then '碧柔/BIORE'
        |when firstCategoryId = '10019' and (brandName_cn = '娜丽丝/NARISUP' or brandName_cn = 'NARISUP')  then "娜丽丝/NARISUP"
        |when firstCategoryId = '10019' and (brandName_cn = 'COPPERTONE' or brandName_cn = '水宝宝' or brandName_cn = '确美同') then '确美同/COPPERTONE'
        |when firstCategoryId = '10019' and (brandName_cn = '怡思丁' or brandName_cn = 'ISDIN' or brandName_cn = "ISDIN/怡思丁") then "怡思丁/ISDIN"
        |when firstCategoryId = '10019' and (brandName_cn = '蜜丝婷' or brandName_cn = 'MISTINE' or brandName_cn rlike '美诗婷')  then "蜜丝婷/MISTINE"
        |when firstCategoryId = '10019' and (brandName_cn = '美肤宝' or brandName_cn = 'MEIFUBAO')  then "美肤宝/MEIFUBAO"
        |when firstCategoryId = '10019' and (brandName_cn = '苏菲娜' or brandName_cn = 'SOFINA')  then "苏菲娜/SOFINA"
        |when firstCategoryId = '10019' and (brandName_cn = 'PROYA' or brandName_cn = '珀莱雅') then '珀莱雅/PROYA'
        |when firstCategoryId = '10019' and (brandName_cn = '怡丽丝尔' or brandName_cn = 'ELIXIR') then '怡丽丝尔/ELIXIR'
        |when firstCategoryId = '10019' and (brandName_cn = '优佳' or brandName_cn = 'ULTRASUN')  then '优佳/ULTRASUN'
        |when firstCategoryId = '10019' and (brandName_cn = '阿特柔斯' or brandName_cn = 'ATREUS') then 'ATREUS'
        |when firstCategoryId = '10019' and (brandName_cn = '雅顿' or brandName_cn rlike 'ELIZABETHARDEN') then '伊丽莎白雅顿/ELIZABETH ARDEN'
        |when firstCategoryId = '10019' and (brandName_cn = 'MARTI DERM' or brandName_cn rlike '玛蒂德肤') then '玛蒂德肤/MARTI DERM'
        |when firstCategoryId = '10019' and (brandName_cn = '娇韵诗' or brandName_cn rlike 'CLARINS') then '娇韵诗/CLARINS'
        |when firstCategoryId = '10019' and (brandName_cn = '芭乐雅' or brandName_cn rlike 'BALEA') then '芭乐雅/BALEA'
        |when firstCategoryId = '10019' and (brandName_cn = '修丽可' or brandName_cn = 'SKINCEUTICALS') then '修丽可/SKINCEUTICALS'
        |when firstCategoryId = '10019' and (brandName_cn = '润百颜' or brandName_cn rlike 'BIOHYALUX') then '润百颜/BIOHYALUX'
        |when firstCategoryId = '10019' and  brandName_cn = '赫莲娜' then '赫莲娜/HR'
        |when firstCategoryId = '10019' and (brandName_cn = '娇兰' or brandName_cn rlike 'GUERLAIN') then '娇兰/GUERLAIN'
        |when firstCategoryId = '10019' and (brandName_cn rlike '韩瑟' or brandName_cn rlike 'HIISEES') then '韩瑟/HIISEES'
        |when firstCategoryId = '10019' and brandName_cn = "钻姿/" then "钻姿"
        |when firstCategoryId = '10019' and brandName_cn = "毕扬" then "毕扬/b´Yeswo"
        |when firstCategoryId = '10019' and brandName_cn = "倾肤/DAII" then "倾肤/D'AII"
        |when firstCategoryId = '10019' and (brandName_cn = '美康粉黛' or brandName_cn rlike 'MEIKING') then '美康粉黛/MEIKING'
        |when firstCategoryId = '10019' and (brandName_cn = '香蒲丽' or brandName_cn rlike 'SHANGPREE') then '香蒲丽/SHANGPREE'
        |when firstCategoryId = '10019' and (brandName_cn = '蛇毒' or brandName_cn rlike '蛇毒眼膜' or brandName_cn rlike 'SPA TREATMENT') then '蛇毒/SPA TREATMENT'
        |when firstCategoryId = '10019' and (brandName_cn = '菲洛嘉' or brandName_cn rlike 'FILORGA') then '菲洛嘉/FILORGA'
        |when firstCategoryId = '10019' and (brandName_cn = '透真' or brandName_cn rlike 'LUCENBASE') then '透真/LUCENBASE'
        |when firstCategoryId = '10019' and (brandName_cn = '海飞丝' or brandName_cn = "HEAD&SHOULDERS") then '海飞丝/HEAD & SHOULDERS'
        |when firstCategoryId = '10019' and (brandName_cn = '阿道夫' or brandName_cn = "ADOLPH")  then '阿道夫/ADOLPH'
        |when firstCategoryId = '10019' and (brandName_cn = '清扬' or brandName_cn = "CLEAR") then '清扬/CLEAR'
        |when firstCategoryId = '10019' and (brandName_cn = '沙宣' or brandName_cn = 'VS')  then '沙宣/VS'
        |when firstCategoryId = '10019' and (brandName_cn = '施华蔻' or brandName_cn = 'SCHWARZKOPF')   then "施华蔻/SCHWARZKOPF"
        |when firstCategoryId = '10019' and (brandName_cn = '潘婷' or brandName_cn = 'PANTENE') then '潘婷/PANTENE'
        |when firstCategoryId = '10019' and (brandName_cn = '飘柔' or brandName_cn = 'REJOICE') then '飘柔/REJOICE'
        |when firstCategoryId = '10019' and (brandName_cn = '滋源' or brandName_cn = 'SEEYOUNG')  then '滋源/SEEYOUNG'
        |when firstCategoryId = '10019' and (brandName_cn = '吕' and brandName_cn = 'RYO') then '吕/RYO'
        |when firstCategoryId = '10019' and (brandName_cn = '霸王' and brandName_cn = 'BAWANG') then '霸王/BAWANG'
        |when firstCategoryId = '10019' and (brandName_cn = '卡诗' and brandName_cn = 'KERASTASE	') then '卡诗/KERASTASE'
        |when firstCategoryId = '10019' and (brandName_cn = '丝蕴' and brandName_cn = 'SYOSS') then '丝蕴/SYOSS'
        |when firstCategoryId = '10019' and (brandName_cn = '惠润' and brandName_cn = 'SUPER MILD') then '惠润/SUPER MILD'
        |when firstCategoryId = '10019' and (brandName_cn = '水之密语' or brandName_cn = 'AQUAIR') then '水之密语/AQUAIR'
        |when firstCategoryId = '10019' and (brandName_cn = '丝蓓绮' or brandName_cn = 'TSUBAKI') then '丝蓓绮/TSUBAKI'
        |when firstCategoryId = '10019' and (brandName_cn = '力士' or brandName_cn = 'LUX')   then '力士/LUX'
        |when firstCategoryId = '10019' and (brandName_cn = '植观' or brandName_cn = 'NATTITUDE') then '植观/NATTITUDE'
        |when firstCategoryId = '10019' and (brandName_cn = '苏菲' or brandName_cn = 'SOFY')  then '苏菲/SOFY'
        |when firstCategoryId = '10019' and (brandName_cn = '护舒宝' or brandName_cn = 'ALWAYS') then '护舒宝'
        |when firstCategoryId = '10019' and (brandName_cn = '七度空间' or brandName_cn = 'SPACE7') then '七度空间/SPACE7'
        |when firstCategoryId = '10019' and (brandName_cn = '高洁丝' or brandName_cn = 'KOTEX') then '高洁丝/KOTEX'
        |when firstCategoryId = '10019' and (brandName_cn = '乐而雅' or brandName_cn = 'LAURIER') then '乐而雅/LAURIER'
        |when firstCategoryId = '10019' and (brandName_cn = 'ABC卫生巾' or  brandName_cn = 'ABC') then 'ABC'
        |when firstCategoryId = '10019' and (brandName_cn = '洁婷' or brandName_cn = 'LADYCARE') then '洁婷/LADYCARE'
        |when firstCategoryId = '10019' and (brandName_cn = '怡丽' or brandName_cn = 'ELIS') then '怡丽/ELIS'
        |when firstCategoryId = '10019' and (brandName_cn = '全棉时代'  or brandName_cn = 'PURCOTTON') then '全棉时代/PURCOTTON'
        |when firstCategoryId = '10019' and (brandName_cn = '飞' or brandName_cn = 'FREE')  then '飞/FREE'
        |when firstCategoryId = '10019' and (brandName_cn = '自由点' or brandName_cn = 'FREEMORE')  then '自由点/FREEMORE'
        |when firstCategoryId = '10019' and (brandName_cn = '千金净雅' or brandName_cn = 'JAYA')  then '千金净雅/JAYA'
        |when firstCategoryId = '10019' and (brandName_cn = '棉密码' or brandName_cn = 'M.PASSWORD')  then '棉密码/M.PASSWORD'
        |when firstCategoryId = '10019' and (brandName_cn = '月月舒' or brandName_cn = 'YUEYUESHU')  then '月月舒/YUEYUESHU'
        |when firstCategoryId = '10019' and (brandName_cn = '薇尔' or brandName_cn = 'VIA') then '薇尔/VIA'
        |when firstCategoryId = '10019' and (brandName_cn = 'BYPHASSE' or brandName_cn = '蓓昂斯') then '蓓昂斯/BYPHASSE'
        |when firstCategoryId = '10019' and (brandName_cn = '贝德玛' or brandName_cn = 'BIODERMA') then '贝德玛/BIODERMA'
        |when firstCategoryId = '10019' and (brandName_cn = '芭妮兰' or brandName_cn = 'BANILA CO') then '芭妮兰/BANILA CO'
        |when firstCategoryId = '10019' and (brandName_cn = '美宝莲' or brandName_cn = 'MAYBELLINE') then '美宝莲/MAYBELLINE'
        |when firstCategoryId = '10019' and (brandName_cn = '娜诗丽' or brandName_cn = 'NURSERY') then '娜诗丽/NURSERY'
        |when firstCategoryId = '10019' and (brandName_cn = '悠宜' or brandName_cn = 'UNNY' or brandName_cn = "悠宜/") then '悠宜/UNNY'
        |when firstCategoryId = '10019' and (brandName_cn = '蝶翠诗' or brandName_cn = 'DHC') then '蝶翠诗/DHC'
        |when firstCategoryId = '10019' and (brandName_cn = '雪玲妃' or brandName_cn = 'XUELINFE' or  brandName_cn = 'SNEFE') then '雪玲妃/SNEFE'
        |when firstCategoryId = '10019' and (brandName_cn = '曼丹' or brandName_cn = 'MANDOM' or brandName_cn = '漫丹 MANDOM') then '曼丹/MANDOM'
        |when firstCategoryId = '10019' and (brandName_cn = '无添加' or brandName_cn = 'FANCL' or brandName_cn = '芳珂') then '芳珂/FANCL'
        |when firstCategoryId = '10019' and (brandName_cn = '植村秀' or brandName_cn = 'SHU-UEMURA' or brandName_cn = 'SHUUEMURA') then '植村秀/SHU UEMURA'
        |when firstCategoryId = '10019' and (brandName_cn = '稚优泉' or  brandName_cn = 'CHIOTURE') then '稚优泉/CHIOTURE'
        |when firstCategoryId = '10019' and (brandName_cn = '凡茜' or  brandName_cn = 'FANXISHOP') then '凡茜/FANXISHOP'
        |when firstCategoryId = '10019' and (brandName_cn = '高丝' or  brandName_cn = 'KOSE') then '高丝/KOSE'
        |when firstCategoryId = '10019' and (brandName_cn = '玛丽黛佳' or brandName_cn = "MARIEDALGAR" or brandName_cn = 'MARIE DALGAR') then '玛丽黛佳/MARIE DALGAR'
        |when firstCategoryId = '10019' and (brandName_cn = 'LANEIGE' or brandName_cn = '兰芝' ) then '兰芝/LANEIGE'
        |when firstCategoryId = '10019' and (brandName_cn = '肌肤之钥' or  brandName_cn = 'CPB') then '肌肤之钥/CPB'
        |when firstCategoryId = '10019' and (brandName_cn = '卡姿兰' or brandName_cn = 'CARSLAN') then '卡姿兰/CARSLAN'
        |when firstCategoryId = '10019' and (brandName_cn = '纪梵希' or brandName_cn = 'GIVENCHY') then '纪梵希/GIVENCHY'
        |when firstCategoryId = '10019' and (brandName_cn = '圣罗兰' or brandName_cn = 'YSL')then '圣罗兰/YSL'
        |when firstCategoryId = '10019' and (brandName_cn = '阿玛尼' or brandName_cn = 'ARMANI' or brandName_cn rlike '乔治·阿玛尼' or brandName_cn rlike '安普里奥阿玛尼') then '阿玛尼/ARMANI'
        |when firstCategoryId = '10019' and (brandName_cn = '谜尚' or brandName_cn = 'MISSHA') then '谜尚/MISSHA'
        |when firstCategoryId = '10019' and (brandName_cn = '井田' or brandName_cn = 'CANMAKE') then '井田/CANMAKE'
        |when firstCategoryId = '10019' and (brandName_cn = '完美日记' or brandName_cn = 'PERFECTDIARY' or brandName_cn = 'PERFECT DIARY')  then '完美日记/PERFECT DIARY'
        |when firstCategoryId = '10019' and (brandName_cn = '姬芮' or brandName_cn = 'Za' or brandName_cn = 'ZA') then '姬芮/Za'
        |when firstCategoryId = '10019' and (brandName_cn = '爱丽小屋' or brandName_cn = 'ETUDEHOUSE '
        |or brandName_cn = '伊蒂之屋' or brandName_cn = 'ETUDE HOUSE')  then "伊蒂之屋/ETUDE HOUSE"
        |when firstCategoryId = '10019'  and (brandName_cn = '奇士美' or brandName_cn = 'KISSME' or brandName_cn = 'KISS ME') then '奇士美/KISS ME'
        |when firstCategoryId = '10019'  and (brandName_cn = '火烈鸟' or brandName_cn = 'FLAMINGO')  then '火烈鸟/FLAMINGO'
        |when firstCategoryId = '10019'  and (brandName_cn = '凯朵' or brandName_cn = 'KANEBO KATE' or brandName_cn = '凯婷'
        |or brandName_cn = '凯特/KATE' or brandName_cn = 'KATE') then '凯婷/KANEBO KATE'
        |when firstCategoryId = '10019'  and (brandName_cn = '健美创研' or brandName_cn = "M'AYCREATE" or brandName_cn = 'MAYCREATE')  then "健美创研/M'AYCREATE"
        |when firstCategoryId = '10019'  and (brandName_cn = '橘朵' or brandName_cn = "JUDYDOLL")  then '橘朵/JUDYDOLL'
        |when firstCategoryId = '10019' and (brandName_cn = '汤姆福特' or brandName_cn = "TOMFORD" or brandName_cn = "TOM FORD") then '汤姆福特/TOM FORD'
        |when firstCategoryId = '10019' and (brandName_cn = '莉兹' or brandName_cn = 'LEEZI') then '莉兹/LEEZI'
        |when firstCategoryId = '10019' and (brandName_cn = '魅可'  or brandName_cn = 'MAC')  then '魅可/MAC'
        |when firstCategoryId = '10019' and (brandName_cn = '迪奥' or brandName_cn = 'DIOR' or brandName_cn = '克里斯汀迪奥') then '迪奥/DIOR'
        |when firstCategoryId = '10019' and (brandName_cn = '香奈儿' or brandName_cn = 'CHANEL' ) then '香奈儿/CHANEL'
        |when firstCategoryId = '10019' and (brandName_cn = '芊美兰'  or brandName_cn = 'CHIVANY') then '芊美兰/CHIVANY'
        |when firstCategoryId = '10019' and (brandName_cn = '蔻静'  or brandName_cn = 'KOUJING') then '蔻静/KOUJING'
        |when firstCategoryId = '10019' and (brandName_cn = '花王' or brandName_cn = 'KAO') then '花王/KAO'
        |when firstCategoryId = '10019' and (brandName_cn = '爱茉莉' or brandName_cn = 'AMORE') then '爱茉莉/AMORE'
        |when firstCategoryId = '10019' and (brandName_cn = '美源' or brandName_cn = 'BIGEN') then '美源/BIGEN'
        |when firstCategoryId = '10019' and (brandName_cn = '章华' or brandName_cn = 'SAVOL')  then '章华/SAVOL'
        |when firstCategoryId = '10019' and (brandName_cn = '利尻昆布' or brandName_cn = 'SASTTY') then '利尻昆布/SASTTY'
        |when firstCategoryId = '10019' and (brandName_cn = '瑞虎' or brandName_cn = 'RUIHU') then '瑞虎/RUIHU'
        |when firstCategoryId = '10019' and (brandName_cn = '海维斯' or brandName_cn = 'HAIWEISI') then '海维斯/HAIWEISI'
        |when firstCategoryId = '10019' and (brandName_cn = '昌义生态' or brandName_cn = 'CHANGYISHENGTAI') then '昌义生态/CHANGYISHENGTAI'
        |when firstCategoryId = '10019' and (brandName_cn = '韩金靓' or brandName_cn = 'HANJINLIANG') then '韩金靓/HANJINLIANG'
        |when firstCategoryId = '10019' and (brandName_cn = '特惠美' or brandName_cn = 'TEHUIMEI') then '特惠美/TEHUIMEI'
        |when firstCategoryId = '10019' and (brandName_cn = '舒肤佳' or brandName_cn = 'SAFEGUARD') then '舒肤佳/SAFEGUARD'
        |when firstCategoryId = '10019' and (brandName_cn = '可悠然' or brandName_cn = 'KUYURA') then '可悠然/KUYURA'
        |when firstCategoryId = '10019' and (brandName_cn = '东方宝石' or brandName_cn = "ESORID`ORIENTE") then "东方宝石/TESORID`ORIENTE"
        |when firstCategoryId = '10019' and (brandName_cn = '六神' or brandName_cn = 'LIUSHEN') then "六神/LIUSHEN"
        |when firstCategoryId = '10019' and (brandName_cn = '阿迪达斯' or brandName_cn = 'ADIDAS') then '阿迪达斯/ADIDAS'
        |when firstCategoryId = '10019' and (brandName_cn = '强生' or brandName_cn = 'JOHNSON') then '强生/JOHNSON'
        |when firstCategoryId = '10019' and (brandName_cn = '菲诗小铺' or brandName_cn = 'THEFACESHOP') then '菲诗小铺/THE FACE SHOP'
        |when firstCategoryId = '10019' and (brandName_cn = '澳宝' or brandName_cn = 'OPAL') then '澳宝/OPAL'
        |when firstCategoryId = '10019' and (brandName_cn = '艾诗' or brandName_cn = 'ENCHANTEUR') then '艾诗/ENCHANTEUR'
        |when firstCategoryId = '10019' and (brandName_cn = '安宝笛' or brandName_cn = 'ONTHEBODY') then '安宝笛/ON THE BODY'
        |when firstCategoryId = '10019' and (brandName_cn = '欧舒丹' or brandName_cn = 'LOCCITANE' or brandName_cn = "L'OCCITANE") then '欧舒丹/LOCCITANE'
        |when firstCategoryId = '10019' and (brandName_cn = '满婷' or brandName_cn = 'MANTING') then '满婷/MANTING'
        |when firstCategoryId = '10019' and (brandName_cn = '沛丽康' or brandName_cn = 'PELICAN' ) then '沛丽康/PELICAN'
        |when firstCategoryId = '10019' and (brandName_cn = '滴露' or brandName_cn = 'DETTOL') then '滴露/DETTOL'
        |when firstCategoryId = '10019' and (brandName_cn = '遇见香芬' or brandName_cn = 'COCOESSENCE' ) then '遇见香芬/COCOESSENCE'
        |when firstCategoryId = '10019' and (brandName_cn = '上海香皂' or brandName_cn = 'SHANGHAIXIANGZAO' ) then '上海香皂/SHANGHAIXIANGZAO'
        |when firstCategoryId = '10019' and (brandName_cn = '隆力奇' or brandName_cn = 'LONGRICH' ) then '隆力奇'
        |when firstCategoryId = '10019' and (brandName_cn = '威露士' or brandName_cn = 'WALCH') then '威露士/WALCH'
        |when firstCategoryId = '10019' and (brandName_cn = '蜂花' or brandName_cn = 'BEE&FLOWER') then '蜂花/BEE&FLOWER'
        |when firstCategoryId = '10019' and (brandName_cn = '半亩花田' or brandName_cn = 'LITTLE DREAM GARDEN') then '半亩花田/LITTLE DREAM GARDEN'
        |when firstCategoryId = '10019' and (brandName_cn = '贺本清' or brandName_cn = 'HERBACIN') then '贺本清/HERBACIN'
        |when firstCategoryId = '10019' and (brandName_cn = '美润' or brandName_cn = 'HAND CREAM') then '美润护手霜/HAND CREAM'
        |when firstCategoryId = '10019' and (brandName_cn = '欧丽源' or brandName_cn = 'OILYOUNG') then '欧丽源/OILYOUNG'
        |when firstCategoryId = '10019' and (brandName_cn = '美加净' or brandName_cn = 'MAXAM') then '美加净/MAXAM'
        |when firstCategoryId = '10019' and (brandName_cn = '凡士林' or brandName_cn = 'VASELINE') then '凡士林/VASELINE'
        |when firstCategoryId = '10019' and (brandName_cn = '屈臣氏' or brandName_cn = 'WATSONS') then '屈臣氏/WATSONS'
        |when firstCategoryId = '10019' and (brandName_cn = '番茄派' or brandName_cn = 'TOMATOPIE') then '番茄派/TOMATOPIE'
        |when firstCategoryId = '10019' and (brandName_cn = '真皙' or brandName_cn = 'JENSY') then '真皙/JENSY'
        |when firstCategoryId = '10019' and (brandName_cn = '祖玛珑'  or brandName_cn = 'JOMALONELONDON'
        |or brandName_cn rlike '祖.玛珑' or brandName_cn rlike 'JO MALONE LONDON') then '祖玛珑/JO MALONE'
        |when firstCategoryId = '10019' and (brandName_cn = '范思哲'  or brandName_cn = 'VERSACE') then '范思哲/VERSACE'
        |when firstCategoryId = '10019' and (brandName_cn = '宝格丽'  or brandName_cn = 'BVLGARI') then '宝格丽/BVLGARI'
        |when firstCategoryId = '10019' and (brandName_cn = '爱马仕'  or brandName_cn = 'HERMES') then '爱马仕/HERMES'
        |when firstCategoryId = '10019' and (brandName_cn = '菲拉格慕'  or brandName_cn = 'FERRAGAMO') then '菲拉格慕/FERRAGAMO'
        |when firstCategoryId = '10019' and (brandName_cn = '博柏利'  or brandName_cn = 'BURBERRY') then '博柏利/BURBERRY'
        |when firstCategoryId = '10019' and (brandName_cn = '名蓝'  or brandName_cn = 'MINGLAN') then '名蓝/MINGLAN'
        |when firstCategoryId = '10019' and (brandName_cn = '冰希黎'  or brandName_cn = 'BOITOWN') then '冰希黎/BOITOWN'
        |when firstCategoryId = '10019' and (brandName_cn = '芬尚'  or brandName_cn = 'FORSHINE') then '芬尚'
        |when firstCategoryId = '10019' and (brandName_cn = '卡尔文克雷恩'  or brandName_cn = 'CALVIN KLEIN') then '卡尔文克雷恩/CALVIN KLEIN'
        |when firstCategoryId = '10019' and (brandName_cn = '气味图书馆'  or brandName_cn = 'SCENTLIBRARY') then '气味图书馆/SCENTLIBRARY'
        |when firstCategoryId = '10019' and (brandName_cn = '汇香坊'  or brandName_cn = 'SCENTHOUSE') then '汇香坊/SCENTHOUSE'
        |when firstCategoryId = '10019' and (brandName_cn = '安娜苏'  or brandName_cn = 'ANNA SUI' or brandName_cn = 'ANNASUI') then '安娜苏/ANNA SUI'
        |when firstCategoryId = '10019' and (brandName_cn = 'GUCCI' or brandName_cn = '古驰') then '古驰/GUCCI'
        |when firstCategoryId = '10019' and (brandName_cn = '云南白药' or brandName_cn = 'YUNNANBAIYAO') then '云南白药/YUNNAN BAIYAO'
        |when firstCategoryId = '10019' and (brandName_cn = '黑人' or brandName_cn = 'DARLIE') then '黑人/DARLIE'
        |when firstCategoryId = '10019' and (brandName_cn = '佳洁士' or brandName_cn = 'CREST')  then "佳洁士/CREST"
        |when firstCategoryId = '10019' and (brandName_cn = '舒客' or brandName_cn = 'SAKY')  then "舒客/SAKY"
        |when firstCategoryId = '10019' and (brandName_cn = '高露洁' or brandName_cn = 'COLGATE') then "高露洁/COLGATE"
        |when firstCategoryId = '10019' and (brandName_cn = '舒适达' or brandName_cn = 'SENSODYNE') then '舒适达/SENSODYNE'
        |when firstCategoryId = '10019' and (brandName_cn = '欧乐B' or brandName_cn = 'ORAL-B') then '欧乐B/ORAL-B'
        |when firstCategoryId = '10019' and (brandName_cn = '皓齿健' or brandName_cn = 'HOSJAM') then '皓齿健/HOSJAM'
        |when firstCategoryId = '10019' and (brandName_cn = '中华' or brandName_cn = 'ZHONGHUA') then '中华/ZHONGHUA'
        |when firstCategoryId = '10019' and (brandName_cn = '小巨蛋' or brandName_cn = 'LITTLEDOME') then '小巨蛋/LITTLEDOME'
        |when firstCategoryId = '10019' and (brandName_cn = '冷酸灵' or brandName_cn = 'LSL') then '冷酸灵/LSL'
        |when firstCategoryId = '10019' and (brandName_cn = '纳美' or brandName_cn = 'NAMEI') then '纳美/NAMEI'
        |when firstCategoryId = '10019' and (brandName_cn = '皓乐齿' or brandName_cn = 'ORA2') then '皓乐齿/ORA2'
        |when firstCategoryId = '10019' and (brandName_cn = '好易康' or brandName_cn = 'HAOYIKANG') then '好易康/HAOYIKANG'
        |when firstCategoryId = '10019' and (brandName_cn = '时克' or brandName_cn = 'SAYCLO') then '时克/SAYCLO'
        |when firstCategoryId = '10019' and (brandName_cn = '时通' or brandName_cn = 'SHITONG') then '时通/SHITONG'
        |when firstCategoryId = '10019' and (brandName_cn = '纽西小精灵' or brandName_cn = 'UKIWI') then '纽西小精灵/UKIWI'
        |----资生堂
        |when firstCategoryId = '10019' and (brandName_cn = '蜜丝菲诗' or brandName_cn = 'MISSFACE' or brandName_cn = 'MISS FACE') then '蜜丝菲诗/MISS FACE'
        |when firstCategoryId = '10019' and brandName_cn = '水博兰' and brandName_cn not rlike '资生堂' then '水博兰/WATBALAN'
        |when firstCategoryId = '10019' and brandName_cn = '扶衡' and brandName_cn not rlike '资生堂' then '扶衡/FUBALANCE'
        |when firstCategoryId = '10019' and brandName_cn = '兰亭' and brandName_cn not rlike '资生堂' then '兰亭/LANTERN'
        |when firstCategoryId = '10019' and brandName_cn = 'COSMETEX ROLAND' and brandName_cn not rlike '资生堂' then 'COSMETEX ROLAND'
        |when firstCategoryId = '10019' and brandName_cn = '衡美肤' and brandName_cn not rlike '资生堂' then '衡美肤/BEAUTY BALANCE'
        |when firstCategoryId = '10019' and brandName_cn = '兰卓丽' and brandName_cn not rlike '资生堂' then '兰卓丽'
        |when firstCategoryId = '10019' and brandName_cn = '恋佳纯' and brandName_cn not rlike '资生堂' then '恋佳纯/LANJESUN'
        |when firstCategoryId = '10019' and brandName_cn = '蓝伯来' and brandName_cn not rlike '资生堂' then '蓝伯来/LANBOTO'
        |when firstCategoryId = '10019' and brandName_cn = '伊诗兰顿' and brandName_cn not rlike '资生堂' then '伊诗兰顿/ISILANDON'
        |when firstCategoryId = '10019' and brandName_cn = '美植兰' and brandName_cn not rlike '资生堂' then '美植兰/MEEZLAN'
        |when firstCategoryId = '10019' and brandName_cn = '诗泊蓝' and brandName_cn not rlike '资生堂' then '诗泊蓝/SPALAND'
        |when firstCategoryId = '10019' and brandName_cn = '露兰姬娜' and brandName_cn not rlike '资生堂' then '露兰姬娜/ROLANJONA'
        |when firstCategoryId = '10019' and brandName_cn = 'FINE LAND' and brandName_cn not rlike '资生堂' then 'FINE LAND'
        |when firstCategoryId = '10019' and brandName_cn = '幽兰' and brandName_cn not rlike '资生堂' then '幽兰/ORLANE'
        |when firstCategoryId = '10019' and brandName_cn = '瑞特莲恩' and brandName_cn not rlike '资生堂' then '瑞特莲恩/LOTUSLAND'
        |when firstCategoryId = '10019' and brandName_cn = '诗婷蒂兰' and brandName_cn not rlike '资生堂' then '诗婷蒂兰/STDLAN'
        |when firstCategoryId = '10019' and brandName_cn = '娇诗兰' and brandName_cn not rlike '资生堂' then '娇诗兰/GELSEALAN'
        |when firstCategoryId = '10019' and brandName_cn = '伊芙蓮' and brandName_cn not rlike '资生堂' then '伊芙蓮/EVE LAND'
        |when firstCategoryId = '10019' and brandName_cn = '香兰阁' and brandName_cn not rlike '资生堂' then '香兰阁/SHARELAND'
        |when firstCategoryId = '10019' and brandName_cn = '朗斯' and brandName_cn not rlike '资生堂' then '朗斯/LANACE'
        |when firstCategoryId = '10019' and brandName_cn = '碧雅诗' and brandName_cn not rlike '资生堂' then '碧雅诗/KESALAN PATHARAN'
        |when firstCategoryId = '10019' and brandName_cn = 'MILANATU' and brandName_cn not rlike '资生堂' then 'MILANATU'
        |when firstCategoryId = '10019' and brandName_cn = 'CLEMENTSKIN PURSLANE' and brandName_cn not rlike '资生堂' then 'CLEMENTSKIN PURSLANE'
        |when firstCategoryId = '10019' and brandName_cn = '俪泉' and brandName_cn not rlike '资生堂' then '俪泉/IVATHERM'
        |when firstCategoryId = '10019' and brandName_cn = '欧萃碧' and brandName_cn not rlike '资生堂' then '欧萃碧/PLANTESYSTEM'
        |when firstCategoryId = '10019' and brandName_cn = 'ARGELAN' and brandName_cn not rlike '资生堂' then 'ARGELAN'
        |when firstCategoryId = '10019' and brandName_cn = '兰希黎' and brandName_cn not rlike '资生堂' then '兰希黎/LANXILI'
        |when firstCategoryId = '10019' and brandName_cn = '兰嘉丝汀' and brandName_cn not rlike '资生堂' then '兰嘉丝汀/LANCASTER'
        |when firstCategoryId = '10019' and (brandName_cn = 'THURSDAY PLANTATION' or brandName_cn = 'THURSDAYPLANTATION')
        |and brandName_cn not rlike '资生堂' then '星期四农庄/THURSDAY PLANTATION'
        |when firstCategoryId = '10019' and brandName_cn = '花皙蔻' and brandName_cn not rlike '资生堂' then '花皙蔻/CLORISLAND'
        |when firstCategoryId = '10019' and brandName_cn = '植物工坊' and brandName_cn not rlike '资生堂' then '植物工坊'
        |when firstCategoryId = '10019' and brandName_cn = '俪兰' and brandName_cn not rlike '资生堂' then '俪兰/LEELAN'
        |when firstCategoryId = '10019' and brandName_cn = '兰瑟' and brandName_cn not rlike '资生堂' then '兰瑟/LANSUR'
        |when firstCategoryId = '10019' and brandName_cn = '蓝秀' and brandName_cn not rlike '资生堂' then '蓝秀'
        |when firstCategoryId = '10019' and brandName_cn = '花肌粹' and brandName_cn not rlike '资生堂' then '花肌粹/FLOWERY LAND'
        |when firstCategoryId = '10019' and brandName_cn = '植物医生' and brandName_cn not rlike '资生堂' then "植物医生/DR．PLANT"
        |when firstCategoryId = '10019' and (brandName_cn = '朴妍' or brandName_cn = 'PUREYES') then '朴妍/PUREYES'
        |when firstCategoryId = '10019' and (brandName_cn = '景天丽雪' or brandName_cn = 'GENALESA') then '景天丽雪/GENALESA'
        |when firstCategoryId = '10019' and (brandName_cn = '马丁' or brandName_cn = 'MARTIN' or brandName_cn = 'MADING')  then '马丁/MADING'
        |when firstCategoryId = '10019' and (brandName_cn = '谜草集' or brandName_cn = 'MICAOJI') then '谜草集/MICAOJI'
        |when firstCategoryId = '10019' and (brandName_cn = '奥黛拉' or brandName_cn = "AUDALA") then '奥黛拉/AUDALA'
        |when firstCategoryId = '10019' and (brandName_cn = '克里斯提·鲁布托' or brandName_cn = "CHRISTIANLOUBOUTIN") then "克里斯提·鲁布托/CHRISTIAN LOUBOUTIN"
        |when firstCategoryId = '10019' and (brandName_cn = '睫美秀' or brandName_cn =  'GEMSHO') then '睫美秀/GEMSHO'
        |when firstCategoryId = '10019' and (brandName_cn = '资生堂'  or brandName_cn = 'Shiseido' or brandName_cn = 'SHISEIDO') then '资生堂/Shiseido'
        |when firstCategoryId = '10019' and (brandName_cn = '欧珀莱' or brandName_cn = 'AUPRES') then '欧珀莱/AUPRES'
        |when firstCategoryId = '10019' and (brandName_cn = '莱珀妮' or brandName_cn =  'LA PRAIRIE' or brandName_cn = 'LAPRAIRIE') then '莱珀妮/LA PRAIRIE'
        |when firstCategoryId = '10019' and (brandName_cn = '宝丽' or brandName_cn =  'POLA') then '宝丽/POLA'
        |when firstCategoryId = '10019' and (brandName_cn = '光芮' or brandName_cn = 'GLO&RAY') then '光芮/GLO&RAY'
        |when firstCategoryId = '10019' and (brandName = '妙根思' or brandName = 'MGS') then '妙根思/MGS'
        |when firstCategoryId = '10019' and brandName_cn = '片仔癀' or brandName_cn = 'PIANZAIHUANG' then '片仔癀/PIANZAIHUANG'
        |when firstCategoryId = '10019' and brandName = '吕妃' then '吕妃/LVFEI'
        |when brandName_cn rlike '其他' or brandName_cn rlike '其它' or brandName_cn = 'OTHER' or brandName_cn = '无' or brandName_cn rlike '见描述'  then '其他/OTHER'
        |
        |else brandName_cn end cn
        |from  first_19
      """.stripMargin)
      .drop("brandName_cn", "brandName_en")
      .withColumnRenamed("cn", "brandName_cn")


    case1

  }
  /*
   * 三、将正则处理过后的品牌表中提取除了10019分类以外的数据，通过case when修改品牌名称
   * 处理10019分类以外的其他分类
   */
  private def brand_mod_case1(datas:DataFrame,spark:SparkSession):DataFrame ={
    val adaa = datas.where("firstCategoryId != '10019'")
        .registerTempTable("first_other")

    val newTable = spark.sqlContext.sql(
      """
        |select *,
        |case
        |--宝宝洗浴
        |when firstCategoryId = "10020" and (brandName_cn = "强生" or brandName_cn = "JOHNSON" or brandName_cn = "强生婴儿") then "强生/JOHNSON"
        |when firstCategoryId = '10020' and (brandName_cn = '艾维诺' or brandName_cn = 'AVEENO' or brandName_cn = '艾惟诺' or brandName_cn = '艾唯诺') then '艾惟诺/AVEENO'
        |when firstCategoryId = '10020' and (brandName_cn = '红色小象' or brandName_cn = 'BABY ELEPHANT') then '红色小象/BABY ELEPHANT'
        |when firstCategoryId = '10020' and (brandName_cn = '青蛙王子' or brandName_cn = 'FROGPRINCE') then '青蛙王子/FROGPRINCE'
        |when firstCategoryId = '10020' and (brandName_cn = '贝亲' or brandName_cn = 'PIGEON') then '贝亲/PIGEON'
        |when firstCategoryId = '10020' and (brandName_cn = '妙思乐' or brandName_cn = 'MUSTELA') then '妙思乐/MUSTELA'
        |when firstCategoryId = '10020' and (brandName_cn = '施巴' or brandName_cn = 'SEBAMED' ) then  '施巴/SEBAMED'
        |when firstCategoryId = '10020' and (brandName_cn = 'SANOSAN' or brandName_cn = '哈罗闪') then '哈罗闪/SANOSAN'
        |when firstCategoryId = '10020' and (brandName_cn = '加州宝宝' or brandName_cn rlike 'CALIFORNIA BABY' or brandName_cn rlike 'CALIFORNIABABY') then '加州宝宝/CALIFORNIA BABY'
        |when firstCategoryId = '10020' and (brandName_cn = '小浣熊' or brandName_cn = 'COATI') then '小浣熊/COATI'
        |when firstCategoryId = '10020' and (brandName_cn = '鳄鱼宝宝' or brandName_cn = 'CROCOBABY')  then "鳄鱼宝宝/CROCOBABY"
        |when firstCategoryId = '10020' and (brandName_cn = '启初' or brandName_cn = 'GIVING') then '启初/GIVING'
        |when firstCategoryId = '10020' and (brandName_cn = 'MAMA&KIDS' or brandName_cn = 'MAMA＆KIDS') then 'MAMA&KIDS'
        |when firstCategoryId = '10020' and (brandName_cn = '子初' or brandName_cn = 'SPRINGBUDS') then '子初/SPRINGBUDS'
        |when firstCategoryId = '10020' and (brandName_cn = '爱护' or brandName_cn = 'CAREFOR') then "爱护/CAREFOR"
        |when firstCategoryId = '10020' and (brandName_cn = 'YUMEIJING' or brandName_cn = '郁美净')  then "郁美净/YUMEIJING"
        |when firstCategoryId = '10020' and (brandName_cn = '好孩子' or brandName_cn = 'GOODBABY')  then '好孩子/GOODBABY'
        |--宝宝护肤
        |when firstCategoryId = '10020' and (brandName_cn = '杏璞' or brandName_cn = 'XINGPU')  then "杏璞/XINGPU"
        |when firstCategoryId = '10020' and (brandName_cn = '松达' or brandName_cn = 'SONGDA') then '松达/SONGDA'
        |when firstCategoryId = '10020' and (brandName_cn = '屁屁乐' or brandName_cn = 'PIPILO' or brandName_cn = 'PIPILE') then '屁屁乐/PIPILO'
        |when firstCategoryId = '10020' and (brandName_cn = '贝倍舒' or brandName_cn = 'BEIBEISHU') then '贝倍舒/BEIBEISHU'
        |when firstCategoryId = '10020' and (brandName_cn = '朋博湾' or brandName_cn = "BONPOINT") then 'BONPOINT'
        |--婴儿纸尿裤
        |when firstCategoryId = '10020' and (brandName_cn = '花王' or brandName_cn = "MERRIES") then '花王/MERRIES'
        |when firstCategoryId = '10020' and (brandName_cn = '帮宝适' or brandName_cn = 'PAMPERS') then '帮宝适/PAMPERS'
        |when firstCategoryId = '10020' and (brandName_cn = '好奇' or brandName_cn = 'HUGGIES') then '好奇/HUGGIES'
        |when firstCategoryId = '10020' and (brandName_cn = '尤妮佳' or brandName_cn = 'MOONY') then '尤妮佳/MOONY'
        |when firstCategoryId = '10020' and (brandName_cn = '大王' or brandName_cn = "GOO．N" or brandName_cn rlike '大王/GOO') then '大王/GOO．N'
        |when firstCategoryId = '10020' and (brandName_cn = '妈咪宝贝' or brandName_cn = "MAMYPOKO" or brandName_cn rlike '妈咪宝贝MONNY') then '妈咪宝贝/MAMYPOKO'
        |when firstCategoryId = '10020' and (brandName_cn = '安儿乐' or brandName_cn = "ANERLE") then '安儿乐/ANERLE'
        |when firstCategoryId = '10020' and (brandName_cn = '雀氏' or brandName_cn = "CHIAUS") then '雀氏/CHIAUS'
        |when firstCategoryId = '10020' and (brandName_cn = '菲比' or brandName_cn = "FITTI") then '菲比/FITTI'
        |when firstCategoryId = '10020' and (brandName_cn = '一朵' or brandName_cn = "IDOWORLD" or brandName_cn rlike 'YIDUO') then '一朵'
        |when firstCategoryId = '10020' and (brandName_cn = '宜婴' or brandName_cn = "YIYING") then '宜婴/YIYING'
        |when firstCategoryId = '10020' and (brandName_cn = '巴布豆' or brandName_cn = 'BOBDOG') then '巴布豆/BOBDOG'
        |when firstCategoryId = '10020' and (brandName_cn = '家得宝' or brandName_cn = 'JIADEIBAO' or brandName_cn rlike 'HOMEBABY') then '家得宝/HOMEBABY'
        |when firstCategoryId = '10020' and (brandName_cn = '爽然' or brandName_cn = 'SHINREA') then '爽然/SHINREA'
        |when firstCategoryId = '10020' and (brandName_cn = '南极人' or brandName_cn = 'NANJIREN') then '南极人/NANJIREN'
        |when firstCategoryId = '10020' and (brandName_cn = '舒比奇' or brandName_cn = 'SUITSKY') then '舒比奇/SUITSKY'
        |when firstCategoryId = '10020' and brandName_cn = 'DEERTING' then '小鹿叮叮'
        |when firstCategoryId = '10020' and (brandName_cn = '泰迪熊' or brandName_cn = 'TEDDYBEAR' or brandName_cn rlike '泰迪熊/TEDD') then '泰迪熊/TEDDYBEAR'
        |when firstCategoryId = '10020' and (brandName_cn = '露安适' or brandName_cn = 'LELCH') then '露安适/LELCH'
        |when firstCategoryId = '10020' and (brandName_cn = '茵茵' or brandName_cn = 'COJIN') then '茵茵/COJIN'
        |--成人纸尿裤
        |when firstCategoryId = '10020' and (brandName_cn = '可靠' or brandName_cn = 'COCO') then '可靠/COCO'
        |when firstCategoryId = '10020' and (brandName_cn = '安而康' or brandName_cn rlike 'ELDERJOY') then '安而康/ELDERJOY'
        |when firstCategoryId = '10020' and (brandName_cn = '包大人' or brandName_cn = 'DR．P') then '包大人/DR．P'
        |when firstCategoryId = '10020' and (brandName_cn = '康乐佳' or brandName_cn = 'KONLEGA') then '康乐佳/KONLEGA'
        |when firstCategoryId = '10020' and (brandName_cn = '康福瑞' or brandName_cn = 'COMFREY') then '康福瑞/COMFREY'
        |when firstCategoryId = '10020' and (brandName_cn = '得伴' or brandName_cn rlike 'DEPEND') then '得伴/DEPEND'
        |when firstCategoryId = '10020' and (brandName_cn = '千芝雅' or brandName_cn = 'KIDSYARD') then '千芝雅/KIDSYARD'
        |when firstCategoryId = '10020' and (brandName_cn = '益百年') then '益百年/居家日用'
        |when firstCategoryId = '10020' and (brandName_cn = '洁来' or brandName_cn = 'CLEANLIFE') then '洁来/CLEANLIFE'
        |when firstCategoryId = '10020' and (brandName_cn = '添宁' or brandName_cn = 'TENA') then '添宁/TENA'
        |when firstCategoryId = '10020' and (brandName_cn = '佳爽爱康' or brandName_cn rlike 'CASOFT') then '佳爽/CASOFT'
        |when firstCategoryId = '10020' and (brandName_cn = '安顾宜' or brandName_cn rlike 'ACTY') then '安顾宜/ACTY'
        |--香水
        |when firstCategoryId = '10020' and  brandName rlike '皮皮乐' then '皮皮乐/PIPILE'
        |when firstCategoryId = '10020' and  brandName_cn rlike 'BEJIROG KIDS' then 'BEJIROG KIDS'
        |when firstCategoryId = '10020' and  brandName_cn rlike 'DUDU CREAM' then 'DUDU CREAM'
        |when firstCategoryId = '10020' and (brandName_cn rlike '鲁茜' or  brandName_cn = 'RÜSCH') then '鲁茜/RÜSCH'
        |when firstCategoryId = '10020' and (brandName_cn rlike '小象妈咪 BABY ELEPHANT MOMMY') then '小象妈咪/BABY ELEPHANT MOMMY'
        |when firstCategoryId = '10020' and (brandName_cn rlike '润本' or brandName_cn rlike  'RUNBEN') then  '润本/RUNBEN'
        |when firstCategoryId = '10020' and (brandName_cn rlike '保宁' or brandName_cn =  'BB'  or brandName_cn  = 'B&B')  then '保宁/BB'
        |when firstCategoryId = '10020' and (brandName_cn rlike '博朗' or brandName_cn rlike  'BRAUN') then '博朗/BRAUN'
        |when firstCategoryId = '10020' and (brandName_cn rlike '甘尼克宝贝' or brandName_cn rlike  'BABY GANICS' or brandName_cn rlike 'BABYGANICS')  then '甘尼克宝贝/BABY GANICS'
        |when firstCategoryId = '10020' and (brandName_cn rlike '五羊' or brandName_cn rlike 'FIVERAMS') then '五羊/FIVERAMS'
        |when firstCategoryId = '10020' and (brandName_cn rlike 'BABYCARE BY PHILEMON' or brandName_cn rlike 'BABYCARE')  then "BABYCARE"
        |when firstCategoryId = '10020' and (brandName_cn rlike '嘉宝' or brandName_cn rlike 'GERBER')  then '嘉宝/GERBER'
        |when firstCategoryId = '10020' and (brandName_cn rlike '英氏' or brandName_cn rlike 'YEEHOO') then '英氏/YEEHOO'
        |when firstCategoryId = '10020' and (brandName_cn rlike '佳思敏' or brandName_cn rlike "NATURE'S WAY") then "佳思敏/NATURE'S WAY"
        |when firstCategoryId = '10020' and (brandName_cn rlike '可爱多' or brandName_cn rlike 'CUTEBABY') then '可爱多/CUTEBABY'
        |when firstCategoryId = '10020' and (brandName_cn rlike '可么多么' or brandName_cn rlike 'COMOTOMO') then '可么多么/COMOTOMO'
        |when firstCategoryId = '10020' and (brandName_cn rlike '甘尼克宝贝' or brandName_cn rlike 'BABYGANICS') then '甘尼克宝贝/BABYGANICS'
        |when firstCategoryId = '10020' and (brandName_cn rlike '凤凰/PHENIX' or brandName_cn rlike '凤凰牌/PHOENIX') then '凤凰/PHOENIX'
        |when firstCategoryId = '10020' and (brandName_cn rlike '北极绒' or brandName_cn rlike 'BEJIROG') then '北极绒/BEJIROG'
        |when firstCategoryId = '10020' and (brandName_cn rlike '奶油' or brandName_cn rlike 'CREAM') then '奶油/CREAM'
        |when firstCategoryId = '10020' and (brandName_cn rlike '凯蒂猫' or brandName_cn rlike 'HELLO KITTY') then '凯蒂猫/HELLO KITTY'
        |when firstCategoryId = '10020' and (brandName_cn rlike '澳兰黛' or brandName_cn rlike 'AOCILENDA') then '澳兰黛/AOCILENDA'
        |when firstCategoryId = '10020' and (brandName_cn rlike '犬印本铺' or brandName_cn rlike 'INUJIRUSHI') then '犬印本铺/INUJIRUSHI'
        |when firstCategoryId = '10020' and (brandName_cn rlike '乐高' or brandName_cn = 'LEGO') then '乐高/LEGO'
        |when firstCategoryId = '10020' and (brandName_cn rlike '万代' or brandName_cn rlike 'BANDAI') then '万代/BANDAI'
        |when firstCategoryId = '10020' and (brandName_cn rlike '启蒙' or brandName_cn rlike 'ENLIGHTEN') then '启蒙/ENLIGHTEN'
        |when firstCategoryId = '10020' and (brandName_cn rlike '童励' or brandName_cn = 'TL') then '童励/TL'
        |when firstCategoryId = '10020' and (brandName_cn rlike '澳乐' or brandName_cn = 'AOLE') then '澳乐/AOLE'
        |when firstCategoryId = '10020' and (brandName_cn rlike '新贝' or brandName_cn = 'NCVI') then '新贝/NCVI'
        |when firstCategoryId = '10016' and (brandName_cn rlike '任天堂' or brandName_cn rlike 'NINTENDO') then '任天堂/NINTENDO'
        |when firstCategoryId = '10016' and (brandName_cn rlike '益思' or brandName_cn rlike 'UPM YES') then '益思/UPM YES'
        |--10017
        |when firstCategoryId = '10017' and (brandName_cn = '精工' or brandName_cn = 'SEIKO')  then '精工/SEIKO'
        |when firstCategoryId = '10017' and (brandName_cn = '金仕盾' or brandName_cn = 'JINSHIDUN') then '金仕盾/JIN SHI DUN'
        |when firstCategoryId = '10017' and (brandName_cn = '卡梭' or brandName_cn = 'KASSAW')  then '卡梭/KASSAW'
        |when firstCategoryId = '10017' and (brandName_cn = '卡西欧' or brandName_cn = 'CASIO')then '卡西欧/CASIO'
        |when firstCategoryId = '10017' and (brandName_cn = '天梭' or brandName_cn = 'TISSOT') then '天梭/TISSOT'
        |when firstCategoryId = '10017' and (brandName_cn = '浪琴' or brandName_cn = 'LONGINES') then '浪琴/LONGINES'
        |when firstCategoryId = '10017' and (brandName_cn = '天王' or brandName_cn = 'TIAN WANG') then '天王/TIAN WANG'
        |when firstCategoryId = '10017' and (brandName_cn = '西铁城' or brandName_cn = 'CITIZEN') then '西铁城/CITIZEN'
        |when firstCategoryId = '10017' and (brandName_cn = '罗西尼' or brandName_cn = 'ROSSINI') then '罗西尼/ROSSINI'
        |when firstCategoryId = '10017' and (brandName_cn = '卡尔文·克莱恩' or brandName_cn = 'CALVIN  KLEIN') then '卡尔文·克莱恩/CALVIN  KLEIN'
        |when firstCategoryId = '10017' and (brandName_cn = '劳力士' or brandName_cn = 'ROLEX')then '劳力士/ROLEX'
        |when firstCategoryId = '10017' and (brandName_cn  ='美度' or brandName_cn = 'MIDO') then '美度/MIDO'
        |when firstCategoryId = '10017' and (brandName_cn = '海鸥' or brandName_cn = 'SEAGULL') then '海鸥/SEAGULL'
        |when firstCategoryId = '10017' and (brandName_cn = '卡地亚' or brandName_cn = 'CARTIER') then '卡地亚/CARTIER'
        |when firstCategoryId = '10017' and (brandName_cn = '冠琴' or brandName_cn = 'GUANQIN') then '冠琴/GUANQIN'
        |when firstCategoryId = '10017' and (brandName_cn = '劳士顿' or brandName_cn = 'ROSDN') then '劳士顿/ROSDN'
        |when firstCategoryId = '10017' and (brandName_cn = '飞亚达' or brandName_cn = 'FIYTA') then '飞亚达/FIYTA'
        |--腕表
        |when firstCategoryId = '10017' and brandName_cn = '瑞贝拉' then '瑞贝拉/REBELA'
        |when firstCategoryId = '10017' and brandName_cn = '格诺（GNOTH）' then '格诺/GNOTH'
        |when firstCategoryId = '10017' and brandName_cn = '佳明' then '佳明/GARMIN'
        |when firstCategoryId = '10017' and brandName_cn = '安普里奥阿玛尼' then '安普里奥阿玛尼/EMPORIOARMANI'
        |when firstCategoryId = '10017' and brandName_cn = '艾戈勒' then '艾戈勒/AGELOCER'
        |when firstCategoryId = '10017' and (brandName_cn = '欧米茄' or brandName_cn = 'OMEGA') then '欧米茄/OMEGA'
        |when firstCategoryId = '10017' and brandName_cn = '时间由你' then '时间由你/TIME2U'
        |when firstCategoryId = '10017' and brandName_cn = '爱康唯' then '爱康唯/ACCUWAY'
        |when firstCategoryId = '10017' and brandName_cn = '天梭' then '天梭/TISSOT'
        |when firstCategoryId = '10017' and (brandName_cn = '金仕盾' or brandName_cn = 'JINSHIDUN') then '金仕盾/JIN SHI DUN'
        |when firstCategoryId = '10017' and (brandName_cn = '卡瓦依' or brandName_cn = 'KAWAI') then '卡瓦依/KAWAI'
        |when firstCategoryId = '10017' and (brandName_cn = '丹尼尔惠灵顿' or brandName = '丹尼尔·惠灵顿' or  brandName_cn rlike 'DANIELWELLINGTON'
        |or  brandName_cn rlike 'DANIEL WELLINGTON' or brandName_cn rlike 'DANIEL WELLINGTON' or brandName_cn = '丹尼尔惠灵顿/DW')  then '丹尼尔惠灵顿/DANIEL WELLINGTON'
        |--10021
        |when firstCategoryId = '10021' and (brandName_cn = '核桃' or brandName_cn = 'BAOPI') then '核桃/BAOPI'
        |when firstCategoryId = '10021' and (brandName_cn = 'WANTWANT' or brandName_cn = '旺旺') then '旺旺/WANTWANT'
        |when firstCategoryId = '10021' and (brandName_cn = '箭牌' or brandName_cn = 'WRIGLEY')  then '箭牌/WRIGLEY'
        |when firstCategoryId = '10021' and (brandName_cn rlike '牛头' or brandName_cn rlike 'NEWTO') then '牛头/NEWTO'
        |when firstCategoryId = '10021' and (brandName_cn rlike '福临门' or brandName_cn rlike 'FULINMEN') then '福临门'
        |when firstCategoryId = '10021' and (brandName_cn rlike '海底捞') then '海底捞'
        |when firstCategoryId = '10021' and (brandName_cn rlike '太古' or brandName_cn rlike 'TAIKOO') then '太古/TAIKOO'
        |when firstCategoryId = '10021' and (brandName_cn rlike '百利' or brandName_cn rlike 'BAILEYS') then '百利/BAILEYS'
        |when firstCategoryId = '10021' and (brandName_cn rlike '中盐' or brandName_cn rlike 'ZHONGYAN') then '中盐/ZHONGYAN'
        |when firstCategoryId = '10021' and (brandName_cn rlike '太太乐' or brandName_cn = 'TTL') then '太太乐/TTL'
        |when firstCategoryId = '10021' and (brandName_cn rlike '中粮' or brandName_cn rlike 'COFCOXIANGXUE') then '中粮香雪/COFCOXIANGXUE'
        |when firstCategoryId = '10021' and (brandName_cn rlike '想念' or brandName_cn rlike 'XIANGNIAN') then '想念/XIANGNIAN'
        |when firstCategoryId = '10021' and (brandName_cn rlike '寿桃' or brandName_cn rlike 'SAU TAO') then '寿桃/SAU TAO'
        |when firstCategoryId = '10021' and (brandName_cn rlike '白象' or brandName_cn rlike 'HAKUZO') then '白象/HAKUZO'
        |when firstCategoryId = '10021' and (brandName_cn rlike '绿帝' or brandName_cn rlike 'GREEN KING') then '绿帝/GREEN KING'
        |when firstCategoryId = '10021' and (brandName_cn rlike '星农联合' or brandName_cn rlike 'SINOONUNION') then '星农联合/SINOONUNION'
        |when firstCategoryId = '10021' and (brandName_cn rlike '红太阳' or brandName_cn rlike 'REDSUN') then '红太阳/REDSUN'
        |when firstCategoryId = '10021' and (brandName_cn rlike '圣王' or brandName_cn rlike 'SAINT KING') then '圣王'
        |when firstCategoryId = '10021' and (brandName_cn rlike '龙凤' or brandName_cn rlike 'LONGFONG') then '龙凤'
        |when firstCategoryId = '10021' and (brandName_cn rlike '华美' or brandName_cn rlike 'HUAMEI') then '华美/HUAMEI'
        |when firstCategoryId = '10021' and (brandName_cn rlike '乳牛' or brandName_cn rlike 'DUTCH COW') then '乳牛/DUTCH COW'
        |when firstCategoryId = '10021' and (brandName_cn rlike '君乐宝' or brandName_cn rlike 'JUNLEBAO') then '君乐宝'
        |when firstCategoryId = '10021' and (brandName_cn rlike '艾尔' or brandName_cn = 'A2') then '艾尔/A2'
        |when firstCategoryId = '10021' and (brandName_cn rlike '贝拉米' or brandName_cn rlike 'BELLAMY') then '贝拉米/BELLAMY'
        |when firstCategoryId = '10021' and (brandName_cn rlike '澳滋' or brandName_cn rlike 'OZ FARM') then '澳滋/OZ FARM'
        |when firstCategoryId = '10021' and (brandName_cn rlike '可爱多' or brandName_cn rlike 'CUTEBABY') then '可爱多/CUTEBABY'
        |when firstCategoryId = '10021' and (brandName_cn rlike '维他奶' or brandName_cn rlike 'VITASOY') then '维他奶/VITASOY'
        |when firstCategoryId = '10021' and (brandName_cn rlike '哈达' or brandName_cn rlike 'HATA') then '哈达/HATA'
        |when firstCategoryId = '10021' and (brandName_cn rlike '玉泉' or brandName_cn rlike 'SCHWEPPES') then '玉泉/SCHWEPPES'
        |when firstCategoryId = '10021' and (brandName_cn rlike '珍珍' or brandName_cn rlike 'JACK NJILL') then '珍珍'
        |when firstCategoryId = '10021' and (brandName_cn rlike '寿全斋' or brandName_cn rlike 'SHOUQUANZHAI') then '寿全斋/SHOUQUANZHAI'
        |when firstCategoryId = '10021' and (brandName_cn rlike '长城' or brandName_cn rlike 'GREATWALL') then '长城/GREATWALL'
        |when firstCategoryId = '10021' and (brandName_cn rlike '麒麟' or brandName_cn rlike 'KIRIN') then '麒麟/KIRIN'
        |when firstCategoryId = '10021' and (brandName_cn = '塔牌' or brandName_cn = '塔' or brandName_cn = '圣塔') then '塔'
        |when firstCategoryId = '10021' and (brandName_cn = '劲./JING' or brandName_cn = '劲' or brandName_cn = '劲牌') then '劲./JING'
        |when firstCategoryId = '10021' and (brandName_cn rlike '大益' or brandName_cn rlike 'TAETEA' ) then '大益/TAETEA'
        |when firstCategoryId = '10021' and (brandName_cn rlike '修正' or brandName_cn rlike 'XIUZHENG' ) then '修正/XIUZHENG'
        |when firstCategoryId = '10021' and (brandName_cn rlike '福鼎白茶' or brandName_cn rlike 'FUDING' ) then '福鼎白茶/FUDING'
        |when firstCategoryId = '10021' and brandName_cn rlike '茅台'  then '茅台/MOUTAI'
        |when firstCategoryId = '10021' and (brandName_cn rlike '雀巢' or brandName_cn rlike 'NESTLE') then '雀巢/NESTLE'
        |when firstCategoryId = '10021' and (brandName_cn rlike '马大姐' or brandName_cn rlike 'SISTER MA') then '马大姐/SISTER MA'
        |when firstCategoryId = '10021' and (brandName_cn rlike '星巴克' or brandName_cn rlike 'STARBUCKS') then '星巴克/STARBUCKS'
        |when firstCategoryId = '10021' and (brandName_cn rlike '洽洽' or brandName_cn rlike 'CHACHEER')  then '洽洽/CHACHEER'
        |when firstCategoryId = '10021' and (brandName_cn rlike '福佳' or brandName_cn rlike 'HOEGAARDEN') then '福佳/HOEGAARDEN'
        |when firstCategoryId = '10021' and (brandName_cn rlike '卡乐比' or brandName_cn rlike 'CALBEE' or brandName_cn rlike '卡乐B') then '卡乐比/CALBEE'
        |when firstCategoryId = '10021' and (brandName_cn rlike '哈尔滨啤酒' or brandName_cn rlike 'HARBIN BEER' or brandName_cn rlike 'HARBINBEER') then '哈尔滨啤酒/HARBIN BEER'
        |when firstCategoryId = '10021' and (brandName_cn rlike '展艺' or brandName_cn rlike 'ART EXHIBITION' or brandName_cn rlike 'ARTEXHIBITION') then '展艺/ART EXHIBITION'
        |when firstCategoryId = '10021' and (brandName_cn rlike '伊利' or brandName_cn rlike 'YILI GROUP' or brandName_cn rlike 'YILIGROUP') then '伊利/YILI'
        |when firstCategoryId = '10021' and (brandName_cn rlike '光明' or brandName_cn rlike 'BRILLIANT') then '光明/BRILLIANT'
        |when firstCategoryId = '10021' and (brandName_cn rlike '郎酒' or brandName_cn = '郎') then '郎酒'
        |when firstCategoryId = '10021' and (brandName rlike '尊乐' or upper(brandName) rlike 'JOHNSONVILLE' or brandName rlike 'Johnsonvile') then '尊乐/JOHNSONVILLE'
        |when firstCategoryId = '10021' and (brandName rlike '喜力' or upper(brandName) rlike 'HEINEKEN') then '喜力/HEINEKEN'
        |when firstCategoryId = '10021' and (brandName rlike '哈尔滨啤酒' or brandName rlike '哈尔滨' or upper(brandName) rlike 'HARBIN BEER') then '哈尔滨啤酒/HARBIN BEER'
        |when firstCategoryId = '10021' and (brandName_cn rlike '燕京' or upper(brandName_cn) rlike 'YANJING') then '燕京/YANJING'
        |when firstCategoryId = '10021' and (brandName_cn rlike '三只松鼠' or brandName_cn rlike 'THREESQUIRRELS' or brandName_cn rlike 'THREE SQUIRRELS') then '三只松鼠/THREE SQUIRRELS'
        |when firstCategoryId = '10021' and (brandName_cn rlike "乐事/LAY" or brandName_cn = '乐事')  then "乐事/LAY’S"
        |when firstCategoryId = '10021' and (brandName_cn rlike '稻香村' or brandName_cn rlike 'DXC') then '稻香村/DXC'
        |when firstCategoryId = '10021' and ( brandName_cn rlike '中原/TRUNG' or brandName_cn rlike '中原G7' or brandName_cn rlike '中原/G'
        |or brandName_cn rlike '中原G7牌' or brandName_cn = '中原' or brandName_cn rlike 'G7中原'
        |or brandName_cn rlike 'G7 COFFEE' or brandName_cn rlike '越南/G7' or brandName_cn = 'G7' or (brandName_cn rlike 'G7' and brandName_cn rlike '咖啡') )then '中原/G7'
        |when firstCategoryId = '10021' and brandName_cn rlike '晶花'  then '晶花/KING FLOWER'
        |when firstCategoryId = '10021' and brandName_cn rlike '益昌老街'  then '益昌老街/AIK CHEONG OLD TOWN'
        |when firstCategoryId = '10021' and (brandName_cn = '乳牛' or brandName_cn = 'DUTCH COW')then '乳牛/DUTCH COW'
        |when firstCategoryId = '10021' and (brandName_cn = 'A2' or brandName_cn rlike '产地/A2' or brandName_cn  rlike 'A2集团' or brandName_cn rlike '新西兰A2' or upper(brandName) = 'A 2') then '艾尔/A2'
        |when firstCategoryId = '10021' and brandName_cn  rlike '美素佳儿' then '美素佳儿/FRISO'
        |when firstCategoryId = '10021' and brandName_cn rlike '绿箭' then '绿箭/DOUBLEMINT'
        |when firstCategoryId = '10021' and (brandName_cn rlike '克拉坎特' or brandName_cn  = 'KDV' or brandName_cn rlike '俄罗斯/KDV') then 'KDV'
        |when firstCategoryId = '10021' and (brandName_cn rlike '龙角散' or brandName_cn = 'RYUKAK') then  '龙角散/RYUKAK'
        |when firstCategoryId = '10021' and (brandName_cn rlike '春光' or brandName_cn = 'CHUN GUANG') then '春光/CHUN GUANG'
        |when firstCategoryId = '10021' and (brandName_cn rlike '可口可乐' or upper(brandName) rlike "COCA-COLA"  or  upper(brandName) rlike "COCA") then '可口可乐/COCA-COLA'
        |when firstCategoryId = '10021' and (brandName_cn rlike '恒大冰泉' or upper(brandName) rlike "EVERGRANDE SPRING" or upper(brandName) rlike "EVERGRANDESPRING")  then '恒大冰泉/EVERGRANDE SPRING'
        |when firstCategoryId = '10021' and (brandName_cn rlike '名仁'  or upper(brandName) rlike "MINGREN")   then '名仁/MINGREN'
        |when firstCategoryId = '10021' and (brandName_cn rlike '鼠大厨' or brandName_cn = 'RAT CHEF' ) then '鼠大厨'
        |when firstCategoryId = '10021' and (brandName_cn rlike '百事可乐' or brandName_cn = 'PEPSI-COLA') then '百事可乐/PEPSI-COLA'
        |when firstCategoryId = '10021' and (brandName_cn rlike '品铺掌柜' or brandName_cn = 'SAMPLEMAN') then '品铺掌柜/SAMPLEMAN'
        |when firstCategoryId = '10021' and (brandName_cn rlike '萨洛缇' or brandName_cn = 'SAROTTI') then '萨洛缇/SAROTTI'
        |when firstCategoryId = '10021' and (brandName_cn rlike "好时/HERSHEY'S" or brandName_cn = '好时' or brandName_cn rlike 'HERSHEYS' ) then "好时/HERSHEY'S"
        |when firstCategoryId = '10021' and (brandName_cn rlike '德芙' or brandName_cn rlike "德芙 'DOVE") then '德芙/DOVE'
        |when firstCategoryId = '10021' and (brandName_cn rlike '诺梵' or brandName_cn rlike "NUOFAN") then '诺梵/NUOFAN'
        |when firstCategoryId = '10021' and (brandName_cn rlike '费列罗' or brandName_cn rlike "FERRERO ROCHER") then '费列罗/FERRERO ROCHER'
        |when firstCategoryId = '10021' and (brandName_cn rlike '歌帝梵' or brandName_cn rlike "GODIVA") then '歌帝梵/GODIVA'
        |when firstCategoryId = '10021' and (brandName_cn rlike '好利来' or brandName_cn rlike "HOLILAND") then '好利来/HOLILAND'
        |when firstCategoryId = '10021' and (brandName_cn rlike '嘉利宝' or brandName_cn rlike "CALLEBAUT") then '嘉利宝/CALLEBAUT'
        |when firstCategoryId = '10021' and (brandName_cn rlike '健达' or brandName_cn rlike "KINDER") then '健达/KINDER'
        |when firstCategoryId = '10021' and (brandName_cn = '莲' or brandName_cn rlike "莲/LINDT" or brandName_cn rlike 'LINDT') then '莲/LINDT'
        |when firstCategoryId = '10021' and (brandName_cn rlike '六神' or brandName_cn rlike "LIUSHEN") then '六神'
        |when firstCategoryId = '10021' and (brandName_cn rlike '玛氏' or brandName_cn = "MARS") then '玛氏/MARS'
        |when firstCategoryId = '10021' and (brandName_cn rlike '明治' or brandName_cn rlike "MEIJI") then '明治/MEIJI'
        |when firstCategoryId = '10021' and (brandName_cn rlike '诺优能' or brandName_cn rlike "NUTRILON") then '诺优能/NUTRILON'
        |when firstCategoryId = '10022' and (brandName_cn rlike '白猫' or brandName_cn rlike 'WHITECAT' or brandName_cn rlike 'WHITE CAT') then '白猫'
        |when firstCategoryId = '10022' and (brandName_cn rlike '心相印' or brandName_cn rlike 'XIN XIANG YIN') then '心相印/XIN XIANG YIN'
        |when firstCategoryId = '10022' and (brandName_cn rlike '立白' or upper(brandName) rlike 'LIBY') then '立白'
        |when firstCategoryId = '10022' and (brandName_cn rlike '卫新' or upper(brandName) rlike "FRESH'N HYGIENE") then "卫新/FRESH'N HYGIENE"
        |when firstCategoryId = '10022' and (brandName_cn rlike '无印良品' or brandName_cn = "MUJI") then '无印良品/MUJI'
        |when firstCategoryId = '10022' and (brandName_cn rlike '雅鹿' or brandName_cn = "YALU") then '雅鹿/YALU'
        |when firstCategoryId = '10022' and (brandName_cn rlike '博洋' or brandName_cn = "BEYOND")  then '博洋/BEYOND'
        |when firstCategoryId = '10022' and (brandName_cn = '雷士' or brandName_cn = "NVC") then '雷士/NVC'
        |when firstCategoryId = '10022' and (brandName_cn rlike '玫斯' or brandName_cn = 'METZ')  then '玫斯/METZ'
        |when firstCategoryId = '10022' and (brandName_cn rlike '福摩' or brandName_cn = 'FROMM') then '福摩/FROMM'
        |when firstCategoryId = '10022' and (brandName_cn rlike '优趣' or brandName_cn = 'YOUCH')  then '优趣/YOUCH'
        |when firstCategoryId = '10022' and (brandName_cn rlike '妙多乐' or brandName_cn = 'CAT CHOW') then '妙多乐/CAT CHOW'
        |when firstCategoryId = '10022' and (brandName_cn rlike '好主人' or brandName_cn = 'CARE') then '好主人/CARE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '麦富迪' or brandName_cn = 'MYFOODIE') then '麦富迪/MYFOODIE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '佩玛思特' or brandName_cn = 'PETMASTER')  then '佩玛思特/PETMASTER'
        |when firstCategoryId = '10022' and (brandName_cn rlike '伯纳天纯'  or brandName_cn = 'PURE&NATURAL') then '伯纳天纯/PURE&NATURAL'
        |when firstCategoryId = '10022' and (brandName_cn rlike '怡亲' or brandName_cn = 'YOKEN')  then '怡亲/YOKEN'
        |when firstCategoryId = '10022' and (brandName_cn rlike '爱肯拿' or brandName_cn = 'ACANA') then '爱肯拿/ACANA'
        |when firstCategoryId = '10022' and (brandName_cn rlike '比瑞吉' or brandName_cn = 'NATURE BRIDGE') then '比瑞吉/NATURE BRIDGE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '耐威克' or brandName_cn = 'NAVARCH')  then '耐威克/NAVARCH'
        |when firstCategoryId = '10022' and (brandName_cn rlike '宝路/PEDIGREE' or brandName_cn = 'PEDIGREE') then '宝路/PEDIGREE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '海洋之星' or brandName_cn = 'FISH4DOGS')  then '海洋之星/FISH4DOGS'
        |when firstCategoryId = '10022' and (brandName_cn rlike '醇粹'  or brandName_cn = 'PURICH') then '醇粹/PURICH'
        |when firstCategoryId = '10022' and (brandName_cn rlike '派得'  or brandName_cn = 'PROMINENT') then '派得/PROMINENT'
        |when firstCategoryId = '10022' and (brandName_cn rlike '雷米高' or brandName_cn = 'RAMICAL')  then '雷米高/RAMICAL'
        |when firstCategoryId = '10022' and (brandName_cn rlike '蓝氏' or brandName_cn = 'LEGENDSANDY') then '蓝氏/LEGENDSANDY'
        |when firstCategoryId = '10022' and (brandName_cn rlike '苏泊尔' or brandName_cn = 'SUPOR') then '苏泊尔/SUPOR'
        |when firstCategoryId = '10022' and (brandName_cn rlike '力狼' or brandName_cn = 'LILANG') then '力狼/LILANG'
        |when firstCategoryId = '10022' and (brandName_cn rlike '伊萨/YEASA' or brandName_cn = 'YEASA') then '伊萨/YEASA'
        |when firstCategoryId = '10022' and (brandName_cn rlike '皇家' or brandName_cn rlike 'ROYAL CANIN')  then '皇家/ROYAL CANIN'
        |when firstCategoryId = '10022' and (brandName_cn rlike '渴望' or brandName_cn rlike 'ORIJEN') then '渴望/ORIJEN'
        |when firstCategoryId = '10022' and (brandName_cn = '伟嘉' or brandName_cn = 'WHISKAS') then '伟嘉/WHISKAS'
        |when firstCategoryId = '10022' and (brandName_cn rlike '冠能' or brandName_cn = 'PRO PLAN') then '冠能/PRO PLAN'
        |when firstCategoryId = '10022' and (brandName_cn rlike '素力高' or brandName_cn = 'SOLIDGOLD')  then '素力高/SOLIDGOLD'
        |when firstCategoryId = '10022' and (brandName_cn = '美士' or brandName_cn = 'NUTRO')  then '美士/NUTRO'
        |when firstCategoryId = '10022' and (brandName_cn rlike '芳新' or brandName_cn = 'FOREVER NEW' or brandName_cn = 'FOREVERNEW') then '芳新/FOREVERNEW'
        |when firstCategoryId = '10022' and (brandName_cn rlike '奇强' or brandName_cn = 'KEON') then '奇强/KEON'
        |when firstCategoryId = '10022' and (brandName_cn rlike '洛娃' or brandName_cn = 'REWARD') then '洛娃/REWARD'
        |when firstCategoryId = '10022' and (brandName_cn rlike '威露士' or brandName_cn = 'WALCH')  then '威露士/WALCH'
        |when firstCategoryId = '10022' and (brandName_cn rlike '滴露' or brandName_cn = 'DETTOL') then '滴露/DETTOL'
        |when firstCategoryId = '10022' and (brandName_cn rlike '金纺' or brandName_cn = 'COMFORT')  then '金纺'
        |when firstCategoryId = '10022' and (brandName_cn rlike '蓝月亮' or brandName_cn = 'BLUEMOON') then '蓝月亮/BLUEMOON'
        |when firstCategoryId = '10022' and (brandName_cn rlike '蓝晴' or brandName_cn rlike 'LANQING') then '蓝晴'
        |when firstCategoryId = '10022' and (brandName_cn rlike '麦德氏' or brandName_cn rlike 'IN-PLUS') then '麦德氏/IN-PLUS'
        |when firstCategoryId = '10022' and (brandName_cn rlike '威猛先生'  or brandName_cn = 'MR MUSCLE' or brandName_cn = 'MRMUSCLE') then '威猛先生/MR MUSCLE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '斧头牌' or brandName_cn = 'AXE') then '斧头牌/AXE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '当妮' or brandName_cn = 'DOWNY') then '当妮/DOWNY'
        |when firstCategoryId = '10022' and (brandName_cn rlike '芳新' or brandName_cn = 'FOREVER NEW' or brandName_cn = 'FOREVERNEW') then '芳新/FOREVERNEW'
        |when firstCategoryId = '10022' and (brandName_cn rlike '俏代美' or brandName_cn = 'QIAODAIMEI') then '俏代美/QIAODAIMEI'
        |when firstCategoryId = '10022' and (brandName_cn rlike '亮碟' or brandName_cn = 'FINISH')  then '亮碟/FINISH'
        |when firstCategoryId = '10022' and (brandName_cn rlike '洁宜佳' or brandName_cn = 'JOYA') then '洁宜佳/JOYA'
        |when firstCategoryId = '10022' and (brandName_cn rlike '绿伞' or brandName_cn = 'LVSAN')  then '绿伞'
        |when firstCategoryId = '10022' and (brandName_cn rlike '老管家' or brandName_cn = 'LAOGUANJIA') then '老管家'
        |when firstCategoryId = '10022' and (brandName_cn rlike '心相印' or brandName_cn rlike 'XIN XIANG YIN' or brandName_cn rlike 'MIND ACT UPON MIND') then '心相印/XIN XIANG YIN'
        |when firstCategoryId = '10022' and (brandName_cn rlike '植护' or brandName_cn = 'ZHIHU') then '植护/ZHIHU'
        |when firstCategoryId = '10022' and (brandName_cn rlike '得宝' or brandName_cn = 'TEMPO' or brandName_cn rlike 'DIPLOMAT') then '得宝/TEMPO'
        |when firstCategoryId = '10022' and (brandName_cn rlike '奕辰' or brandName_cn = 'YICHEN') then '奕辰/YICHEN'
        |when firstCategoryId = '10022' and (brandName_cn rlike '蓝漂' or brandName_cn rlike 'LAMPURE') then '蓝漂/LAMPURE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '舒洁' or brandName_cn rlike 'KLEENEX') then '舒洁/KLEENEX'
        |when firstCategoryId = '10022' and (brandName_cn rlike '立白' or brandName_cn = 'LIBY') then '立白'
        |when firstCategoryId = '10022' and (brandName_cn rlike '汰渍' or brandName_cn = 'TIDE') then '汰渍/TIDE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '奥妙' or brandName_cn = 'OMO') then '奥妙/OMO'
        |when firstCategoryId = '10022' and (brandName_cn rlike '碧浪' or brandName_cn = 'ARIEL') then '碧浪/ARIEL'
        |when firstCategoryId = '10022' and  brandName_cn rlike '洁霸'  then '洁霸/ATTACK'
        |when firstCategoryId = '10022' and (brandName_cn rlike '花王' or brandName_cn = 'KAO') then '花王/KAO'
        |when firstCategoryId = '10022' and (brandName_cn rlike '好爸爸' or brandName_cn = 'KISPA') then '好爸爸/KISPA'
        |when firstCategoryId = '10022' and (brandName_cn rlike '奥妙' or brandName_cn = 'OMO') then '奥妙/OMO'
        |when firstCategoryId = '10022' and (brandName_cn rlike "CHAONENG") then '超能'
        |when firstCategoryId = '10022' and (brandName_cn rlike '佳通' or brandName_cn = 'GITI') then '佳通/GITI'
        |when firstCategoryId = '10022' and (brandName_cn rlike '锦湖' or brandName_cn = 'KUMHO') then '锦湖/KUMHO'
        |when firstCategoryId = '10022' and (brandName_cn rlike '摩安珂' or brandName_cn = "MO&CO.") then "摩安珂/MO&CO."
        |when firstCategoryId = '10022' and (brandName_cn rlike '丹琳美卡' or brandName_cn rlike 'DANIMAYKA') then '丹琳美卡/DANIMAYKA'
        |when firstCategoryId = '10022' and (brandName_cn rlike '恒洁卫浴' or brandName_cn rlike 'HEGII')  then '恒洁/HEGII'
        |when firstCategoryId = '10022' and (brandName_cn rlike 'CLé DE PEAU BEAUTé') then 'CLE DE PEAU BEAUTE'
        |when firstCategoryId = '10022' and (brandName_cn rlike '现代' or brandName_cn rlike 'HYUNDAI')  then '现代/HYUNDAI'
        |when firstCategoryId = '10022' and (brandName_cn rlike '旅行便携/TO TO' or brandName_cn rlike 'TO TO' or brandName_cn rlike 'TOTO') then 'TOTO'
        |when firstCategoryId = '10022' and (brandName_cn rlike '佳能')  then '佳能/CANON'
        |when firstCategoryId = '10022' and (brandName_cn rlike 'SHEENFOTO' or brandName_cn rlike '星富图')  then '星富图/SHEENFOTO'
        |when firstCategoryId = '10022' and (brandName_cn rlike '菜百')   then '菜百/CBAI'
        |when firstCategoryId = '10025' and brandName_cn rlike 'MUSCLETECH' then '麦斯泰克/MUSCLETECH'
        |when firstCategoryId = '10025' and (brandName_cn rlike 'HEALTHY CARE' or brandName_cn rlike '葡萄籽') then '葡萄籽/HEALTHY CARE'
        |when firstCategoryId = '10025' and (brandName_cn rlike 'ISDG/爱诗迪' or brandName_cn rlike '医食同源' or brandName_cn = 'ISDG')  then '医食同源/ISDG'
        |when firstCategoryId = '10025' and (brandName_cn rlike '维骨力' or brandName_cn rlike 'SCHIFF MOVEFREE') then '维骨力/SCHIFF MOVEFREE'
        |when firstCategoryId = '10025' and (brandName_cn rlike '旭福' or brandName_cn rlike 'SCHIFF')  then '旭福/SCHIFF'
        |when firstCategoryId = '10025' and brandName_cn rlike '可视眸' then '可视眸/NEO'
        |when firstCategoryId = '10025' and (brandName_cn rlike '相模' or brandName_cn rlike 'SAGAMI') then '相模/SAGAMI'
        |when firstCategoryId = '10025' and (brandName_cn rlike '尚牌' or brandName_cn rlike 'ELASUN') then '尚牌/ELASUN'
        |when firstCategoryId = '10025' and brandName_cn rlike '罗氏'  then '罗氏/ROCHE'
        |when firstCategoryId = '10025' and (brandName_cn rlike 'BAYER' or brandName_cn rlike '拜耳') then '拜耳/BAYER'
        |when firstCategoryId = '10025' and (brandName_cn rlike '朝美' or brandName_cn = 'CM' or brandName_cn rlike '创美') then '朝美/CM'
        |when firstCategoryId = '10025' and (brandName_cn rlike '霍尼韦尔' or brandName_cn rlike 'HONEYWELL') then '霍尼韦尔/HONEYWELL'
        |when firstCategoryId = '10025' and (brandName_cn rlike 'KOBAYASHI' or brandName_cn rlike '小林' or brandName_cn rlike '小林制药') then '小林制药/KOBAYASHI'
        |when firstCategoryId = '10025' and upper(brandName) rlike 'NEO LENS'  then 'NEO LENS'
        |when firstCategoryId = '10025' and (brandName_cn rlike '强生' or brandName_cn rlike 'JOHNSON') then '强生/JOHNSON'
        |when firstCategoryId = '10025' and upper(brandName) rlike 'WARMPLUS'    then 'WarmPlus'
        |when firstCategoryId = '10025' and (brandName_cn rlike '泰邦'  or upper(brandName) rlike 'ATEX')   then 'ATEX'
        |when firstCategoryId = '10025' and (brandName_cn rlike '伊暖儿'  or upper(brandName) rlike 'E·WARMER' or upper(brandName) rlike 'E.WARMER')   then '伊暖儿/E.WARMER'
        |when firstCategoryId = '10025' and (brandName_cn rlike '名创优品'  or upper(brandName) rlike 'MINISO')   then '名创优品'
        |when firstCategoryId = '10025' and (brandName_cn rlike '珍视明' ) then '珍视明'
        |when firstCategoryId = '10025' and (brandName_cn rlike '安耳悠' or brandName_cn rlike 'OHRFRIEDEN') then '安耳悠/OHRFRIEDEN'
        |when firstCategoryId = '10025' and (brandName_cn rlike '名创优品' or brandName_cn rlike 'MINISO') then '名创优品'
        |when firstCategoryId = '10025' and (brandName_cn rlike '酷凯通' or brandName_cn rlike "COOKIETONG") then '酷凯通/COOKIE TONG'
        |when firstCategoryId = '10025' and (brandName_cn rlike 'WARMPLUS' ) then 'WarmPlus'
        |when firstCategoryId = '10025' and (brandName_cn rlike '暖友' ) then '暖友'
        |when firstCategoryId = '10025' and brandName_cn rlike 'SALUS' then '莎露斯/SALUS'
        |when firstCategoryId = '10025' and brandName_cn rlike 'FANCL' then '芳珂/FANCL'
        |when firstCategoryId = '10025' and brandName_cn rlike '安利' then '安利/AMWAY'
        |when firstCategoryId = '10025' and (brandName_cn rlike '汤臣倍健' or brandName_cn rlike 'BY-HEALTH')  then '汤臣倍健/BY-HEALTH'
        |when firstCategoryId = '10025' and brandName_cn rlike '康比特'  then '康比特/CPT'
        |when firstCategoryId = '10025' and brandName_cn rlike 'NATURADE' then 'NATURADE'
        |when firstCategoryId = '10025' and brandName_cn rlike '安琪纽特' then '安琪纽特/ANGEL NUTRITECH'
        |when firstCategoryId = '10025' and brandName_cn rlike '燕之典' then '燕之典/YANDEAR'
        |when firstCategoryId = '10025' and brandName_cn rlike 'SWISSE' then '瑞思/SWISSE'
        |
        |else brandName_cn end cn
        |from
        |first_other
      """.stripMargin)
      .drop("brandName_cn")
      .withColumnRenamed("cn", "brandName_cn")

    newTable
  }
  /**
    * 四、将10019分类和其他分类的表进行合并
    * 这个里面的CASE_WHEN是一些不是按照分类拆分的品牌名称
    */

  private def special_name(data:DataFrame,spark:SparkSession):DataFrame={
    data.registerTempTable("brandplatdata")
    /**
      * 这个是处理品牌的stap_7的case_when
      */
    val case1 = spark.sqlContext.sql(
      """
        |select *,
        |case
        |when  brandName_cn = "大润发(FP)" or brandName_cn = "大润发/ACTUEL" or brandName_cn = "大润发/" or brandName_cn = '大润发/DISNEY'  then "大润发/RT-MART"
        |when  brandName_cn = '康迪' or brandName_cn = 'KATADYN' then '康迪/KATADYN'
        |when  brandName_cn = '艾洛迪' or brandName_cn = 'ELODIE DETAILS' or brandName_cn = 'ELODIEDETAILS' then '艾洛迪/ELODIE DETAILS'
        |when  brandName_cn = '云南白药' or brandName_cn = 'YUNNAN BAIYAO' or brandName_cn = 'YUNNANBAIYAO' then '云南白药/YUNNAN BAIYAO'
        |when  brandName_cn = '杏璞' or brandName_cn = 'XINGPU'  then '杏璞/XINGPU'
        |when  brandName_cn = '佳洁士' or brandName_cn = 'CREST' then '佳洁士/CREST'
        |when  brandName_cn = '谢嘉儿' or brandName_cn = 'XIEEXCELLENT' then '谢嘉儿/XIEEXCELLENT'
        |when  brandName_cn = '巴拉巴拉' or brandName_cn = 'BALABALA' then '巴拉巴拉/BALABALA'
        |when  brandName_cn = '太子龙' or brandName_cn = "TEDELON" then '太子龙/TEDELON'
        |when  brandName_cn = '韩依诺' or brandName_cn = "HANENUO" then '韩依诺/HANENUO'
        |when  brandName_cn = '咪呢妈咪' or brandName_cn = "MINIMUM" then '咪呢妈咪/MINIMUM'
        |when  brandName_cn = '电动牙刷' or brandName_cn = 'USMILE' then 'USMILE'
        |when  brandName_cn = '和美' or brandName_cn = 'HERE MEGA' then '和美/HERE MEGA'
        |when  brandName_cn = '赫尔' or brandName_cn = 'HERR' then '赫尔/HERR'
        |when  brandName_cn = '百刷宝贝' or brandName_cn = 'BRUSHBABY' then '百刷宝贝/BRUSHBABY'
        |when  brandName_cn = '赛馥' or brandName_cn = 'SEAFAR' then '赛馥/SEAFAR'
        |when  brandName_cn = '途尊' or brandName_cn = 'KINGTRIP' then '途尊/KINGTRIP'
        |when  brandName_cn = '哈皮卡' or brandName_cn = 'HAPICA' then '哈皮卡/HAPICA'
        |when  brandName_cn = '罗曼' or brandName_cn = 'ROAMAN' then '罗曼/ROAMAN'
        |when  brandName_cn = '哈皮卡' or brandName_cn = "HAPICA" then '哈皮卡/HAPICA'
        |when  brandName_cn = '智艾宝' or brandName_cn = 'AI FOR KIDS' then '智艾宝/AI FOR KIDS'
        |when  brandName_cn = '纳美' or brandName_cn = 'NAMEI' then '纳美/NAMEI'
        |when  brandName_cn = '奇异鸟' or brandName_cn = 'KIWIBIRD' then '奇异鸟/KIWIBIRD'
        |when  brandName_cn = '欧可林' or brandName_cn = 'OCLEAN' then '欧可林/OCLEAN'
        |when  brandName_cn = '同洁' or brandName_cn = 'JOINCARE'  then '同洁/JOINCARE'
        |when  brandName_cn = '赫尔' or brandName_cn = 'HERR'  then '赫尔/HERR'
        |when  brandName_cn = '奥鼎康' or brandName_cn = 'ODINK'  then '奥鼎康/ODINK'
        |when  brandName_cn = '舒克' or brandName_cn = 'SUKE'  then '舒克/SUKE'
        |when  brandName_cn = '澳乐比' or brandName_cn = 'AOLBEA' then '澳乐比/AOLBEA'
        |when  brandName_cn = '绿姿' or brandName_cn = 'LUZI'  then '绿姿/LUZI'
        |when  brandName_cn = '个人护理' or brandName_cn = 'AIYA'  then '个人护理/AIYA'
        |when  brandName_cn = '托马斯&朋友' or brandName_cn = 'THOMAS&FRIENDS'  then '托马斯&朋友/THOMAS&FRIENDS'
        |when  brandName_cn = '葵联' or brandName_cn = 'KUILIAN'  then '葵联/KUILIAN'
        |when  brandName_cn = '膜法世家' or brandName_cn = 'MASKFAMILY'  then '膜法世家/MASKFAMILY'
        |when  brandName_cn = '阿芙' or brandName_cn = 'AFU'  then '阿芙/AFU'
        |when  brandName_cn = '晶钻风' or brandName_cn = 'JZFON'  then '晶钻风/JZFON'
        |when  brandName_cn = '施家乐' or brandName_cn = 'SKYERA'  then '施家乐/SKYERA'
        |when  brandName_cn = '松下' or brandName_cn = 'PANASONIC'  then '松下/PANASONIC'
        |when  brandName_cn = '鸿冠' or brandName_cn = 'HON&GUAN' then '鸿冠珂兴/HON&GUAN'
        |when  brandName_cn = '施家乐' or brandName_cn = 'SKYERA'  then '施家乐/SKYERA'
        |when  brandName_cn = '金像' or brandName_cn = 'GOLDENSTATUEBRAND'  then '金像/GOLDENSTATUEBRAND'
        |when  brandName_cn = '奥鼎康' or brandName_cn = 'ODINK'  then '奥鼎康/ODINK'
        |when  brandName_cn = '米风' or brandName_cn = 'MIWIND'   then '米风/MIWIND'
        |when  brandName_cn = '西奥多' or brandName_cn = 'THEODOOR'  then '西奥多/THEODOOR'
        |when  brandName_cn = '帅友' or brandName_cn = 'SCSAYUU'  then '帅友/SCSAYUU'
        |when  brandName_cn = '洁碧' or brandName_cn = 'WATERPIK'  then '洁碧/WATERPIK'
        |when  brandName_cn = '咪妮妈咪' or brandName_cn = 'MINIMUM' then '咪妮妈咪/MINIMUM'
        |when  brandName_cn = '狮王' or brandName_cn = 'LION' then '狮王/LION'
        |when  brandName_cn = '黑人' or brandName_cn = 'DARLIE' then '黑人/DARLIE'
        |when  brandName_cn = '奇堡鲨鱼' or brandName_cn = 'QIBAO&SHARK' then '奇堡鲨鱼/QIBAO&SHARK'
        |when  brandName_cn = '今唇' or brandName_cn = 'JONE CHENNR' then '今唇/JONE CHENNR'
        |when  brandName_cn = '艾玛娇' or brandName_cn = 'EULMAJOY' then '艾玛娇/EULMAJOY'
        |when  brandName_cn = '骆驼' or brandName_cn = 'CAMEL' then '骆驼/CAMEL'
        |when  brandName_cn = '耐克' or brandName_cn = 'NIKE' then '耐克/NIKE'
        |when  brandName_cn = '阿迪达斯' or brandName_cn = 'ADIDAS' then '阿迪达斯/ADIDAS'
        |when  brandName_cn = '安踏' or brandName_cn = 'ANTA' then '安踏/ANTA'
        |when  brandName_cn = '李宁' or brandName_cn = 'LI-NING' or brandName_cn = 'LINING' then '李宁/LI-NING'
        |when  brandName_cn = '斯凯奇' or brandName_cn = 'SKECHERS' then '斯凯奇/SKECHERS'
        |when  brandName_cn = '特步' or brandName_cn = 'XTEP' then '特步/XTEP'
        |when  brandName_cn = '新百伦' or brandName_cn = 'NEW BALANCE' or brandName_cn = 'NEWBALANCE' then '新百伦/NEW BALANCE'
        |when  brandName_cn = 'AIR JORDAN' or brandName_cn = 'AIRJORDAN' then '乔丹/AIR JORDAN'
        |when  brandName_cn = '亚瑟士' or brandName_cn = 'ASICS' then '亚瑟士/ASICS'
        |when  brandName_cn = '彪马' or brandName_cn = 'PUMA' then '彪马/PUMA'
        |when  brandName_cn = '万斯' or brandName_cn = 'VANS' then '万斯/VANS'
        |when  brandName_cn = '鸿星尔克' or brandName_cn = 'ERKE' then '鸿星尔克/ERKE'
        |when  brandName_cn = '回力' or brandName_cn = 'WARRIOR' then '回力/WARRIOR'
        |when  brandName_cn = '美津浓' or brandName_cn = 'MIZUNO' then '美津浓/MIZUNO'
        |when  brandName_cn = '锐步' or brandName_cn = 'REEBOK' then '锐步/REEBOK'
        |when  brandName_cn = '南极人' or brandName_cn = 'NANJIREN' then '南极人/NANJIREN'
        |when  brandName_cn = '优衣库' or brandName_cn = 'UNIQLO' then '优衣库/UNIQLO'
        |when  brandName_cn = '富贵鸟' or brandName_cn = 'FUGUINIAO' then '富贵鸟/FUGUINIAO'
        |when  brandName_cn = '恒源祥' or brandName_cn = 'HENGYUANXIANG' then '恒源祥/HENGYUANXIANG'
        |when  brandName_cn = '森马' or brandName_cn = 'SENMA' then '森马/SENMA'
        |when  brandName_cn = '海澜之家' or brandName_cn = 'HLA' then '海澜之家/HLA'
        |when  brandName_cn = '七匹狼' or brandName_cn = 'SEPTWOLVES' then '七匹狼/SEPTWOLVES'
        |when  brandName_cn = '战地吉圃' or brandName_cn = 'ZHAN DI JI PU' or brandName_cn = 'ZHANDIJIPU' then '战地吉圃/ZHAN DI JI PU'
        |when  brandName_cn = '太平鸟' or brandName_cn = 'PEACEBIRD' then '太平鸟/PEACEBIRD'
        |when  brandName_cn = '马克华菲' or brandName_cn = 'FAIRWHALE' then '马克华菲/FAIRWHALE'
        |when  brandName_cn = '吉普' or brandName_cn = 'JEEP' then '吉普/JEEP'
        |when  brandName_cn = '冠军' or brandName_cn = 'CHAMPION' then '冠军/CHAMPION'
        |when  brandName_cn = '吉普盾' or brandName_cn = 'NIANJEEP' then '吉普盾/NIANJEEP'
        |when  brandName_cn = '猎威' or brandName_cn = 'LIEWEI' then '猎威/LIEWEI'
        |when  brandName_cn = '蔻驰' or brandName_cn = 'COACH' then '蔻驰/COACH'
        |when  brandName_cn = '稻草人' or brandName_cn = 'MEXICAN' then '稻草人/MEXICAN'
        |when  brandName_cn = '品高' or brandName_cn = 'PINKO' then '品高/PINKO'
        |when  brandName_cn = '老人头' or brandName_cn = 'LAORENTOU' then '老人头/LAORENTOU'
        |when  brandName_cn = '卓梵阿玛尼' or brandName_cn = 'JOSEPHAMANI' then '卓梵阿玛尼/JOSEPHAMANI'
        |when  brandName_cn = '迈克·科尔斯' or brandName_cn = 'MICHAEL KORS' then '迈克·科尔斯/MICHAEL KORS'
        |when  brandName_cn = '芙拉' or brandName_cn = 'FURLA' then '芙拉/FURLA'
        |when  brandName_cn = '迪士尼' or brandName_cn = 'DISNEY' then '迪士尼/DISNEY'
        |when  brandName_cn = '卡帝乐鳄鱼' or brandName_cn = 'CARTELO' then '卡帝乐鳄鱼/CARTELO'
        |when  brandName_cn = '皮尔卡丹' or brandName_cn = 'PIERRE CARDIN' then '皮尔卡丹/PIERRE CARDIN'
        |when  brandName_cn = '日默瓦' or brandName_cn = 'RIMOWA' then '日默瓦/RIMOWA'
        |when  brandName_cn = '乔伦公馆' or brandName_cn = 'QIAOLUNGONGGUAN' then '乔伦公馆/QIAOLUNGONGGUAN'
        |when  brandName_cn = '南极人' or brandName_cn = 'NANJIREN' then '南极人/NANJIREN'
        |when  brandName_cn = '维达' or brandName_cn = 'VINDA' then '维达/VINDA'
        |when  brandName_cn = '洁柔' or brandName_cn = '洁柔/CS' then '洁柔/C&S'
        |when  brandName_cn = '威露士' or brandName_cn = 'WALCH' then '威露士/WALCH'
        |when  brandName_cn = '托里伯奇' or brandName_cn = 'TORY BURCH' or brandName_cn = 'TORYBURCH' then '托里伯奇/TORY BURCH'
        |when  brandName_cn = '路易威登' or brandName_cn = 'LOUIS VUITTON' or brandName_cn = 'LOUISVUITTON' then '路易威登/LOUIS VUITTON'
        |when  brandName_cn = '杰克琼斯' or brandName_cn = 'JACK JONES' or brandName_cn = 'JACKJONES' then '杰克琼斯/JACK JONES'
        |when  brandName_cn = '美特斯邦威' or brandName_cn = 'METERS BONWE' or brandName_cn = 'METERSBONWE'then '美特斯邦威/METERS BONWE'
        |when  brandName_cn = '花花公子' and brandName_cn = '花花公子贵宾'or brandName_cn = 'PLAYBOY' then '花花公子/PLAYBOY'
        |when  brandName_cn = '鬼塚虎' or brandName_cn = 'ONITSUKA TIGER' or brandName_cn = 'ONITSUKATIGER' then '鬼塚虎/ONITSUKA TIGER'
        |when  brandName_cn = '乔丹' or brandName_cn = 'NIKE AIR JORDAN' or brandName_cn = 'NIKEAIRJORDAN' then '乔丹/AIR JORDAN'
        |when  brandName_cn = '香奈儿' or brandName_cn = 'CHANEL' then '香奈儿/CHANEL'
        |when  brandName_cn = '莫杰' or brandName_cn = 'MARC JACOBS' or brandName_cn = 'MARCJACOBS' then '莫杰/MARC JACOBS'
        |when  brandName_cn = '便捷者' or brandName_cn = 'BEXEZE' then '便捷者/BEXEZE'
        |when  brandName_cn = '高露洁'or brandName_cn = 'COLGATE' then '高露洁/COLGATE'
        |when  brandName_cn = '舒客' or brandName_cn = 'SAKY' then '舒客/SAKY'
        |--手机
        |when brandName_cn rlike '鑫荣耀' or upper(brandName) rlike 'X-HONOR' then '鑫荣耀/X-HONOR'
        |when brandName_cn = '荣耀' or upper(brandName) = 'HONOR' then '荣耀/HONOR'
        |when brandName_cn = '苹果' or brandName_cn = 'APPLE' then '苹果/APPLE'
        |when brandName_cn = '华为' or brandName_cn = 'HUAWEI' then '华为/HUAWEI'
        |when brandName_cn = '小米' or brandName_cn = 'MI' or brandName_cn = 'XIAOMI' or brandName_cn rlike 'XIAOMI/小米' or brandName_cn rlike '小米电器' then '小米/MI'
        |when brandName_cn = '三星' or brandName_cn = 'SAMSUNG' then '三星/SAMSUNG'
        |when brandName_cn = '魅族' or brandName_cn = 'MEIZU' then '魅族/MEIZU'
        |when brandName_cn = '一加' or brandName_cn = 'ONEPLUS' then '一加/ONEPLUS'
        |when brandName_cn = '诺基亚' or brandName_cn = 'NOKIA' then '诺基亚/NOKIA'
        |when brandName_cn = '锤子' or brandName_cn = 'SMARTISAN' then '锤子/SMARTISAN'
        |when brandName_cn = '努比亚' or brandName_cn = 'NUBIA' then '努比亚/NUBIA'
        |when brandName_cn = '美图' or brandName_cn = 'MEITU' then '美图/MEITU'
        |when brandName_cn = '联想' or brandName_cn = 'LENOVO' then '联想/LENOVO'
        |when brandName_cn = '飞利浦' or brandName_cn = 'PHILIPS' then '飞利浦/PHILIPS'
        |when brandName_cn = '纽曼' or brandName_cn = 'NEWMAN' then '纽曼/NEWMAN'
        |when brandName_cn = '天语' or upper(brandName) = 'K-TOUCH' then '天语/K-TOUCH'
        |when brandName_cn = '中兴' or brandName_cn = 'ZTE' then '中兴/ZTE'
        |when brandName = 'ZUK' then 'ZUK'
        |--top100
        |when brandName_cn = "美的" or brandName_cn = "MIDEA" or brandName_cn rlike "MIDEA（美的）" or brandName_cn = "美的生活/MADE IN LIFE" then "美的/MIDEA"
        |when brandName_cn = "海尔" or brandName_cn = "HAIER/" or brandName_cn = "HAIER" or brandName_cn rlike "海尔海尔"  then "海尔/HAIER"
        |when brandName_cn = "荣耀" or brandName_cn = "HONOR"  then "荣耀/HONOR"
        |when brandName_cn = "华为" or brandName_cn = "华为/" or brandName_cn = "华为/HCNP" or brandName_cn rlike "HUAWEI华为" or  brandName_cn rlike "华为华为" or
        |brandName_cn = "华为/P30" or brandName_cn rlike "华为HUAWEI"  then "华为/HUAWEI"
        |when brandName_cn rlike "华为" and (brandName_cn rlike '青春版' or brandName_cn rlike "保时捷" or brandName_cn rlike '手机') then "华为/HUAWEI"
        |when brandName_cn rlike "VIVO" and (brandName_cn rlike '步步高' or brandName_cn rlike 'IQOO' or brandName_cn rlike 'PLUS' or brandName_cn rlike '手机')then "VIVO"
        |when brandName_cn = "华为P7" then "华为/HUAWEI"
        |when brandName_cn = "格力高" then "格力高/GLICO"
        |when brandName_cn = "格力科技" or brandName_cn = "格力" or brandName_cn = "格力/GELE" or brandName_cn rlike "格力电器" or brandName_cn = "GREE" then "格力/GREE"
        |when brandName_cn = "海信" or brandName_cn rlike "海信海信" or brandName_cn = "HISENSE" then "海信/HISENSE"
        |when brandName_cn = "小天鹅" or brandName_cn = "LITTLE SWAN" then "小天鹅/LITTLE SWAN"
        |when brandName_cn = "九阳" or brandName_cn rlike "九阳豆浆" or brandName_cn = "九阳/"  then "九阳/JOYOUNG"
        |when brandName_cn = "容声" or brandName_cn = "RONSHEN"  then "容声/RONSHEN"
        |when brandName_cn = "优衣库" or brandName_cn = "UNIQLO"  then "优衣库/UNIQLO"
        |when brandName_cn = "康佳" or brandName_cn = "KONKA/康佳" or brandName_cn = "康佳/KOKNA" or brandName_cn = "KONKA" then "康佳/KONKA"
        |when brandName_cn = "恒源祥" or brandName_cn = "HENGYUANXIANG" then "恒源祥/HENGYUANXIANG"
        |when brandName_cn = "美菱/" or brandName_cn = "美菱" or brandName_cn = "美菱/MEILING" or brandName_cn = "美菱冰箱" then "美菱/MELING"
        |when brandName_cn = "七匹狼" or brandName_cn = "SEPTWOLVES" then "七匹狼/SEPTWOLVES"
        |when brandName_cn = "老板" or brandName_cn = "ROBAM"  then "老板/ROBAM"
        |when brandName_cn = "良品铺子" or brandName_cn = "BESTORE" then "良品铺子/BESTORE"
        |when brandName_cn = "伊利" or brandName_cn = "伊利/YILI GROUP" or brandName_cn = "伊利(YILI GROUP)" or brandName_cn = "伊利/AQUA" then "伊利/YILI"
        |when brandName_cn = "方太" or brandName_cn = "FOTILE"  then "方太/FOTILE"
        |when brandName_cn = "VINDA/维达" or brandName_cn = "维达" or brandName_cn = "VINDA" then "维达/VINDA"
        |when brandName_cn = "战神/HASEE GOD OF WAR" or brandName_cn rlike "神舟笔记本" or brandName_cn = "神舟" or brandName_cn = "HASEE" then "神舟/HASEE"
        |when brandName_cn = "FLYCO" or brandName_cn = "飞科" then "飞科/FLYCO"
        |when brandName_cn = "VATTI/华帝" or brandName_cn = "VATTI" or brandName_cn = "华帝"  then "华帝/VATTI"
        |when brandName_cn = "蓝月亮" or brandName_cn = "BLUEMOON" then "蓝月亮/BLUEMOON"
        |when brandName_cn = "茅台迎宾酒" or brandName_cn = "茅台" or brandName_cn = "MOUTAI" or brandName_cn = "飞天" then "茅台/MOUTAI"
        |when brandName_cn = "波司登" or brandName_cn = 'BOSIDENG' then "波司登/BOSIDENG"
        |when brandName_cn = "WARRIOR" or brandName_cn = "WARRIOR/" or brandName_cn = "回力" then "回力/WARRIOR"
        |when brandName_cn rlike "雅鹿自由自在" or brandName_cn rlike "雅鹿男装/YALU UOMO" or brandName_cn = "雅鹿/" or brandName_cn rlike "品新颖雅鹿羽" or brandName_cn = "雅鹿"
        |or brandName_cn rlike "雅鹿·自由自在" or brandName_cn = "雅鹿女裤" or brandName_cn = "贵族一派雅·鹿" or brandName_cn = "雅鹿·纯彩地带" or brandName_cn = "雅绿/YALU"
        |or brandName_cn = "YALU" then "雅鹿/YALU"
        |when brandName_cn = "清风/BREEZE AUDIO" or brandName_cn = "清风" or brandName_cn = "BREEZE" or brandName_cn = "清风/COOL BREEZE" then "清风/BREEZE"
        |when brandName_cn = "PEACEBIRD/太平鸟" or brandName_cn = "太平鸟" or brandName_cn = "PEACEBIRD"  then "太平鸟/PEACEBIRD"
        |when brandName_cn = "五粮液股份永不分梨" or brandName_cn = "五粮液" or brandName_cn = "WULIANGYE" then "五粮液/WULIANGYE"
        |when brandName_cn = "九　牧" or brandName_cn = "九牧科技" or brandName_cn = "JOMOO" or brandName_cn = "九牧" then "九牧/JOMOO"
        |when brandName_cn = "九牧王" or brandName_cn = "九牧王/" or brandName_cn = "九牧王/JOMMONE" or brandName_cn = "九牧王/JOMUKING" or brandName_cn = "九牧王花洒" then "九牧王/JOEONE"
        |when brandName_cn = "万和/" or brandName_cn = "万/和" or brandName_cn = "万和/WANHEDA" then "万和/VANWARD"
        |when brandName_cn = "荣事达" or brandName_cn = "ROYALSTAR" then "荣事达/ROYALSTAR"
        |when brandName_cn = "YANGHE" or brandName_cn = "洋河"  then "洋河/YANGHE"
        |when brandName_cn = "农夫山泉桶装水" then "农夫山泉"
        |when brandName_cn = "俞兆林" or brandName_cn rlike "俞兆林（YUZHAOLIN）" then "俞兆林/YUZHAOLIN"
        |when brandName_cn = "飞鹤" or brandName_cn = "FIRMUS" then "飞鹤/FIRMUS"
        |when brandName_cn = "周生生" or brandName_cn = "CHOW SANG SANG"  then "周生生/CHOW SANG SANG"
        |when brandName_cn = "红蜻蜓儿童/KIDS" or brandName_cn = "REDDRAGONFLY" or brandName_cn = "红蜻蜓"  then "红蜻蜓/REDDRAGONFLY"
        |when brandName_cn = "澳柯玛" or brandName_cn = "澳柯玛(AUCMA)"  then "澳柯玛/AUCMA"
        |when brandName_cn = "JMGO" or brandName_cn = "坚果"  then "坚果/JMGO"
        |when brandName_cn = "汾酒30年/30" then "汾酒"
        |when brandName_cn = "永久/FOREVER C" or brandName_cn = "永久" or brandName_cn = "FOREVER" or brandName_cn = "山水/MW" then "永久/FOREVER"
        |when brandName_cn = "凤凰" then "凤凰/PHENIX"
        |when brandName_cn = "七格格商城" or brandName_cn = "七格格泳衣" or brandName_cn = "OTHERMIX" then "七格格/OTHERMIX"
        |when brandName_cn = "三只松鼠" or brandName_cn = "THREE SQUIRRELS"  then "三只松鼠/THREE SQUIRRELS"
        |when brandName_cn = "PUPPY" or brandName_cn = "小狗" then "小狗/PUPPY"
        |when brandName_cn = "茵曼" or brandName_cn = "INMAN/茵曼" or brandName_cn = "茵曼/NMAN" or brandName_cn = "INMAN"  then "茵曼/INMAN"
        |when brandName_cn = "十月妈咪" or brandName_cn = "OCTMAMI" then "十月妈咪/OCTMAMI"
        |when brandName_cn = "全棉时代/" or brandName_cn = "全棉时代" or brandName_cn = "PURCOTTON" then "全棉时代/PURCOTTON"
        |when brandName_cn = "DJI 大疆" or brandName_cn = "DJI大疆" or brandName_cn = "DJI大疆精灵" or brandName_cn = "大疆" or brandName_cn = "大疆无人机" or brandName_cn = "DJI" then "大疆/DJI"
        |when brandName_cn = "科大讯飞2.0"  or brandName_cn = "科大讯飞" or brandName_cn = "IFLYTEK" then "科大讯飞/IFLYTEK"
        |when brandName_cn = "小熊/" or brandName_cn = "小熊" or brandName_cn = "BEAR"  then "小熊/BEAR"
        |when brandName_cn = "极米" or brandName_cn = "XGIMI" or brandName_cn = "极米科技/XBYGIMI" then "极米/XGIMI"
        |when brandName_cn = "猫王" then "猫王收音机"
        |when brandName_cn = "鱼跃" or brandName_cn = "YUYUE" then "鱼跃/YUYUE"
        |--mine
        |when firstCategoryId = '10011' and (brandName_cn rlike 'GUCCI' or brandName_cn rlike '古驰' or brandName_cn rlike '古奇') then '古驰/GUCCI'
        |when firstCategoryId = '10011' and (brandName_cn rlike '佐约克' or brandName_cn rlike 'ZOO YORK') then '佐约克/ZOO YORK'
        |when firstCategoryId = '10011' and (brandName_cn rlike '青婉田' or brandName_cn rlike "Q.VONTON") then "青婉田/Q.VONTON"
        |when firstCategoryId = '10011' and (brandName_cn rlike '森马潮男' or brandName_cn rlike '森马简约' or brandName_cn rlike '森马男士青年学生' or brandName_cn = '森马' or brandName_cn rlike '森马服饰') then '森马/SEMIR'
        |--10014
        |when firstCategoryId = '10014' and (brandName_cn rlike 'HYUNDAI' or brandName_cn rlike '现代') then "现代/HYUNDAI"
        |when firstCategoryId = '10014' and (brandName_cn rlike '大宇' or brandName_cn = 'DAEWOO') then '大宇/DAEWOO'
        |when firstCategoryId = '10014' and (brandName_cn rlike '大金' or brandName_cn = 'DAIKIN') then '大金/DAIKIN'
        |when firstCategoryId = '10014' and (brandName_cn rlike '新迎燕' or brandName_cn = 'XYINGYAN') then '新迎燕/XYINGYAN'
        |when firstCategoryId = '10014' and brandName_cn rlike '三菱铅笔' then '三菱/MITSUBISHI'
        |when firstCategoryId = '10014' and (brandName_cn rlike '凯驰' or brandName_cn rlike '卡赫' or brandName_cn rlike 'KARCHER' or brandName_cn rlike '凯驰' or brandName_cn rlike '卡赫' or brandName_cn rlike 'KARCHER') then '卡赫/KARCHER'
        |when firstCategoryId = '10014' and (brandName_cn rlike '三菱电机' or brandName_cn rlike 'MITSUBISHI ELECTRIC') then '三菱电机/MITSUBISHI ELECTRIC'
        |when firstCategoryId = '10014' and (brandName_cn rlike '金三洋' or brandName_cn rlike 'JENSANY') then '金三洋/JENSANY'
        |when firstCategoryId = '10014' and (brandName_cn rlike '交大蓝天' or brandName_cn rlike 'JIAODALANTIAN') then '交大蓝天/JIAODALANTIAN'
        |when firstCategoryId = '10014' and (brandName_cn rlike '东芝' or brandName_cn rlike 'TOSHIBA') then '东芝/TOSHIBA'
        |when firstCategoryId = '10014' and (brandName_cn = '沁园' or brandName_cn  rlike '沁园/QU' or brandName_cn  = 'QINYUAN')then '沁园/QINYUAN'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn rlike '伊莱克斯' or brandName_cn rlike 'ELECTROLUX') then '伊莱克斯/ELECTROLUX'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '莱克' or brandName_cn = 'LEXY') then '莱克/LEXY'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '奥克斯' or brandName_cn = 'AUX')then '奥克斯/AUX'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '新科商用'  or brandName_cn  rlike '新科空调') then '新科/SHINCO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '志高' or brandName_cn = 'CHIGO') then '志高/CHIGO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '西门子' or brandName_cn = 'SIEMENS') then '西门子/SIEMENS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '博士' or brandName = 'BOSE')  then '博士/BOSE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '雅马哈' or brandName = 'YAMAHA') then '雅马哈/YAMAHA'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '红双喜' or brandName_cn = 'RED DOUBLE HAPPINESS') then '红双喜/RED DOUBLE HAPPINESS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '钻石' or brandName_cn = 'DIAMOND') then '钻石/DIAMOND'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '金正' or brandName_cn = 'NINTAUS') then '金正/NINTAUS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '霍尼韦尔' or brandName_cn = 'HONEYWELL') then '霍尼韦尔/HONEYWELL'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '亚都' or brandName_cn = 'YADU') then '亚都/YADU'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '杰维诺' or brandName_cn = 'AVEENO') then '艾惟诺/AVEENO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '扬子' and brandName_cn = 'YAIR') then '扬子/YAIR'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '杰诺' or brandName_cn = 'JARROW')  then '杰诺/JARROW'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '布朗' or brandName_cn = 'BLLC')then '布朗/BLLC'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '百奥' or brandName_cn = 'PARKOO') then '百奥/PARKOO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '先锋' or brandName_cn = 'SINGFUN') then '先锋/SINGFUN'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '半球' or brandName_cn = 'PESKOE') then '半球/PESKOE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '奔腾' or brandName_cn rlike 'POVOS奔腾1'
        |or brandName_cn rlike '奔腾开关' or brandName_cn rlike '奔腾1' or brandName_cn rlike '奔腾7300' or brandName_cn rlike '奔腾POVOS/奔腾' or brandName_cn rlike '奔腾无源低音炮') then '奔腾/POVOS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '惠人' or brandName_cn = 'HUROM') then '惠人/HUROM'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '维他美仕' or brandName_cn = 'VITAMIX') then '维他美仕/VITAMIX'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '卡士' or brandName_cn = '卡士妥') then '卡士/COUSS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '摩飞' or brandName_cn = 'MORPHYRICHARDS') then '摩飞/MORPHYRICHARDS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '虎牌' or brandName_cn = 'TIGER') then '虎牌/TIGER'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '威泰戈' or brandName_cn = 'WHITETIGER') then '威泰戈/WHITETIGER'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = 'YOUXUE' or brandName_cn = '优雪') then '优雪/YOUXUE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '咪咕' or brandName_cn = 'MIGOO') then '咪咕/MIGOO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '奈斯派索' or brandName_cn = 'NESPRESSO')  then '奈斯派索/NESPRESSO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '惠家' or brandName_cn = 'WELHOME')  then '惠家/WELHOME'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '佳能' or brandName_cn = 'CANON') then '佳能/CANON'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '罗技' or brandName_cn = 'LOGITECH') then '罗技/LOGITECH'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = 'HTC VIVE' or brandName_cn = '宏达') then '宏达/HTC VIVE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '九号机器人' or brandName_cn = 'NINEBOT') then '九号机器人/NINEBOT'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '米熊'  or brandName_cn  = 'MI-BEA')  then '米熊/MI-BEA'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (upper(brandName) rlike 'MACHENIKE' or brandName rlike '机械师')  then '机械师/MACHENIKE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '华硕' or brandName_cn rlike 'ASUS')  then '华硕/ASUS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '微软' or brandName_cn rlike 'MICROSOFT') then '微软/MICROSOFT'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '外星人' or upper(brandName) rlike 'ALIENWARE')  then '外星人/ALIENWARE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '水星' or brandName_cn rlike 'MERCURY') then '水星/MERCURY'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '普联' or brandName_cn = 'TP-LINK') then '普联/TP-LINK'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '迅捷' or brandName_cn = 'FAST') then '迅捷/FAST'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '华三' or brandName_cn = 'H3C') then '华三/H3C'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = 'TENDA' or brandName_cn = '腾达')   then '腾达/TENDA'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '宝利通' or brandName_cn = 'POLYCOM') then '宝利通/POLYCOM'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '艾默生' or brandName_cn = 'EMERSON') then '艾默生/EMERSON'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '图腾' or brandName_cn = 'TOTEN') then '图腾/TOTEN'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '索尼' or brandName_cn = 'SONY') then '索尼/SONY'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = 'PONER SAUND' or brandName_cn = '轰天炮') then '轰天炮/PONER SAUND'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '夏普' or brandName_cn = 'SHARP') then '夏普/SHARP'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '惠普' or brandName_cn = 'HP') then '惠普/HP'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '天威' or brandName_cn = 'PRINT-RITE') then '天威/PRINT-RITE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '爱普生' or brandName_cn = 'EPSON') then '爱普生/EPSON'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '爱宝' or brandName_cn = 'AIBAO') then '爱宝/AIBAO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '富士通' or brandName_cn = 'FUJITSU') then '富士通/FUJITSU'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '得力' or brandName_cn = 'DELI') then '得力/DELI'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '凌美' or brandName_cn = 'LAMY') then '凌美/LAMY'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '百乐' or brandName_cn = 'PILOT') then '百乐/PILOT'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '英雄' or brandName_cn = 'HERO') then '英雄/HERO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '派克' or brandName_cn = 'PARKER') then '派克/PARKER'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '真彩' or brandName_cn = 'TRUECOLOR') then '真彩/TRUECOLOR'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '国誉' or brandName_cn = 'KOKUYO') then '国誉/KOKUYO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '中控' or brandName_cn = 'ZKSOFTWARE')  then '中控/ZKSOFTWARE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '卡西欧' or brandName_cn = 'CASIO') then '卡西欧/CASIO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '齐心' or brandName_cn = 'COMIX') then '齐心/COMIX'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '富士施乐' or brandName_cn = 'FUJIXEROX') then '富士施乐/FUJIXEROX'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '米家' or brandName_cn = 'MIJIA') then '米家/MIJIA'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '凯易欧' or brandName_cn = 'KAIYIOU') then '凯易欧/KAIYIOU'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '韩巨' or brandName_cn = "H∧NJU") then "韩巨/H∧NJU"
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '史密斯' or brandName_cn = "A．O．SMITH") then "史密斯/A．O．SMITH"
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '素乐' or brandName_cn = 'SOLOVE') then '素乐/SOLOVE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '先科' or brandName_cn = 'SAST') then '先科/SAST'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '莱维特' or brandName_cn = 'LEWITT')  then '莱维特/LEWITT'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '南孚' or brandName_cn = 'NANFU')  then '南孚/NANFU'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '乐心' or brandName_cn = 'LIFESENSE')  then '乐心/LIFESENSE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '智伴' or brandName_cn = 'ZIB') then '智伴/ZIB'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '佳洁士' or brandName_cn = 'CREST')  then '佳洁士/CREST'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '李施德林' or brandName_cn = 'LISTERINE')  then '李施德林/LISTERINE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (upper(brandName) = 'THINKPAD' or upper(brandName) = 'THINK PAD') then 'THINKPAD'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '斐珞尔' or  upper(brandName_cn) = 'FOREO') then '斐珞尔/FOREO'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '施耐德' or brandName_cn = 'SCHNEIDER ELECTRIC' or brandName_cn = 'SCHNEIDERELECTRIC') then '施耐德/SCHNEIDER ELECTRIC'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and brandName_cn = '海尔施特劳斯' then '海尔施特劳斯/HAIER STRAUSS'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '臣半球' or brandName_cn = 'PESKR') then '臣半球/PESKR'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '槑半球' or brandName_cn = "PAOUHE") then '槑半球/PAOUHE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '口半球' or brandName_cn = "PESKE") then '口半球/PESKE'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '奥米康' or brandName_cn = "AO MI KANG") then '奥米康/AO MI KANG'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '金稻' or brandName_cn = 'KINGDOM' or brandName_cn = 'KINGDOM CARES' or brandName_cn = 'KINGDOM') then '金稻'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and brandName_cn rlike 'LG身体乳' then 'LG'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (brandName_cn = '汉斯希尔' or brandName_cn = 'SYR' or brandName_cn rlike '汉斯/SYR') then '汉斯希尔/SYR'
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and  brandName_cn = '万家乐' then '万家乐/MACRO'
        |when firstCategoryId = '10014' and (upper(brandName) = '小鸭牌' or upper(brandName) = 'XIAOYAPAI') then '小鸭牌/XIAOYAPAI'
        |when firstCategoryId = '10014' and (upper(brandName) = '碧然德' or upper(brandName) = 'BRITA') then '碧然德/BRITA'
        |when firstCategoryId = '10014' and (upper(brandName) = '亨纳斯' or upper(brandName) = 'HOMENICE') then '亨纳斯/HOMENICE'
        |when firstCategoryId = '10014' and (upper(brandName) = '菱博士' or upper(brandName) = 'LINGBOSHI') then '菱博士/LINGBOSHI'
        |when firstCategoryId = '10014' and (upper(brandName) = '海牌' or upper(brandName) = 'HAIPAI') then '海牌/HAIPAI'
        |when firstCategoryId = '10014' and (upper(brandName) = '三角牌' or upper(brandName) = 'TRIANGLE') then '三角牌/TRIANGLE'
        |when firstCategoryId = '10014' and (upper(brandName) = '北美电器' or upper(brandName) = 'ACA') then '北美电器/ACA'
        |when firstCategoryId = '10014' and (upper(brandName) = '尚铭电器' or upper(brandName) rlike 'SMING') then '尚铭电器/SMING'
        |when firstCategoryId = '10014' and (brandName_cn = '暴风' or brandName_cn = 'BAOFENG') then '暴风/BAOFENG'
        |when firstCategoryId = '10014' and (brandName_cn = '威力' or brandName_cn = 'WEILI') then '威力/WEILI'
        |when firstCategoryId = '10014' and (brandName_cn = '韩电' or brandName_cn = 'KEG') then '韩电/KEG'
        |when firstCategoryId = '10014' and (brandName_cn = '三菱' or brandName_cn = 'MITSUBISHI')  then '三菱/MITSUBISHI'
        |when firstCategoryId = '10014' and (brandName_cn = '雪花' or brandName_cn = 'SNOW') then '雪花/SNOW'
        |when firstCategoryId = '10014' and (brandName_cn = '白雪' or brandName_cn = 'SNOWSCREN') then '白雪/SNOWSCREN'
        |when firstCategoryId = '10014' and (brandName_cn = '骆驼' or brandName_cn = 'CAMEL') then '骆驼/CAMEL'
        |when firstCategoryId = '10014' and (brandName_cn = '远大' or brandName_cn = 'BROAD') then '远大/BROAD'
        |when firstCategoryId = '10014' and (brandName_cn = '韩夫人' or brandName_cn = 'HANFUREN') then '韩夫人/HANFUREN'
        |when firstCategoryId = '10014' and (brandName_cn = '安吉尔' or brandName_cn = 'ANGEL') then '安吉尔/ANGEL'
        |when firstCategoryId = '10014' and (brandName_cn = '韩派' or brandName_cn = 'HAP') then '韩派/HAP'
        |when firstCategoryId = '10014' and (brandName_cn = '生活元素' or brandName_cn = 'LIFE ELEMENT') then '生活元素/LIFE ELEMENT'
        |when firstCategoryId = '10014' and (brandName_cn = '小南瓜' or brandName_cn = 'XIAO NAN GUA') then '小南瓜/XIAO NAN GUA'
        |when firstCategoryId = '10014' and (brandName_cn = '新功' or brandName_cn = 'SEKO') then '新功/SEKO'
        |when firstCategoryId = '10014' and (brandName_cn = '依立' or brandName_cn = 'YILI') then '依立/YILI'
        |when firstCategoryId = '10014' and (brandName_cn = '天际' or brandName_cn = 'TONZE') then '天际/TONZE'
        |when firstCategoryId = '10014' and (brandName_cn = '小南瓜' or brandName_cn = 'XIAO NAN GUA') then '小南瓜/XIAO NAN GUA'
        |when firstCategoryId = '10014' and (brandName_cn = '领锐' or brandName_cn = 'LINGRUI') then '领锐/LINGRUI'
        |when firstCategoryId = '10014' and (brandName_cn = '荣事达' or brandName_cn = 'ROYALSTAR') then '荣事达/ROYALSTAR'
        |when firstCategoryId = '10014' and (brandName_cn = '苏泊尔' or brandName_cn = 'SUPOR') then '苏泊尔/SUPOR'
        |when firstCategoryId = '10014' and (brandName_cn = '乐尔康' or brandName_cn = 'LE ER KANG') then '乐尔康/LE ER KANG'
        |when firstCategoryId = '10014' and (brandName_cn = '璐瑶' or brandName_cn = 'LUYAO') then '璐瑶/LUYAO'
        |when firstCategoryId = '10014' and (brandName_cn = '茗振' or brandName_cn = 'MZ') then '茗振/MZ'
        |when firstCategoryId = '10014' and (brandName_cn = '奥佳华' or brandName_cn = 'OGAWA') then '奥佳华/OGAWA'
        |when firstCategoryId = '10014' and (brandName_cn = '倍轻松' or brandName_cn = 'BREO') then '倍轻松/BREO'
        |when firstCategoryId = '10014' and (brandName_cn = '拜尔' or brandName_cn = 'BEPANTHEN') then '拜尔/BEPANTHEN'
        |when firstCategoryId = '10014' and (brandName_cn = '嗨车族' or brandName_cn = 'HICHEZU') then '嗨车族/HICHEZU'
        |when firstCategoryId = '10014' and (brandName_cn = '凤凰' or brandName_cn = 'PHOENIX') then '凤凰/PHOENIX'
        |when firstCategoryId = '10014' and (brandName_cn = '360儿童') then '360'
        |when firstCategoryId = '10014' and (brandName_cn = '亚马逊' or brandName_cn = 'AMAZON') then '亚马逊/AMAZON'
        |when firstCategoryId = '10015' and (brandName_cn = '波尼' or brandName_cn = 'PONY') then '波尼/PONY'
        |when firstCategoryId = '10015' and (brandName_cn = '小蚁' or brandName_cn = 'YI') then '小蚁/YI'
        |when firstCategoryId = '10015' and (brandName_cn = '爱科技' or brandName_cn = 'AKG') then '爱科技/AKG'
        |when firstCategoryId = '10015' and (brandName_cn = '金士顿' or brandName_cn = 'KINGSTON') then '金士顿/KINGSTON'
        |when firstCategoryId = '10015' and (upper(brandName) = '千幻魔镜' or upper(brandName) = 'VR SHINECON') then 'VR SHINECON'
        |when firstCategoryId = '10015' and (upper(brandName) = '优学派' or upper(brandName) = 'YOUXUEPAI') then '优学派/YOUXUEPAI'
        |--电脑办公
        |when firstCategoryId = '10016' and (brandName_cn = '神舟' or brandName_cn = 'HASEE')  then "神舟/HASEE"
        |when firstCategoryId = '10016' and (brandName_cn = '技嘉' or brandName_cn = 'GIGABYTE')  then "技嘉/GIGABYTE"
        |when firstCategoryId = '10016' and (brandName_cn = '美商海盗船' or brandName_cn = 'USCORSAIR')  then "美商海盗船/USCORSAIR"
        |when firstCategoryId = '10016' and (brandName_cn = '英伟达' or brandName_cn = 'NVIDIA')  then "英伟达"
        |when firstCategoryId = '10016' and (brandName_cn = '乐光' or brandName_cn = 'LEGUANG')  then "乐光/LEGUANG"
        |when firstCategoryId = '10016' and (brandName_cn = '友讯' or brandName_cn = 'D-LINK')  then "友讯/D-LINK"
        |when firstCategoryId = '10016' and (brandName_cn = '佳博' or brandName_cn = 'GPRINTER')  then "佳博/GPRINTER"
        |when firstCategoryId = '10016' and (brandName_cn = '半导体' or brandName_cn = 'TSC')  then "半导体/TSC"
        |when firstCategoryId = '10016' and (brandName_cn = '硕方' or brandName_cn = 'SUPVAN')  then "硕方/SUPVAN"
        |when firstCategoryId = '10016' and (brandName_cn = '启锐' or brandName_cn = 'QIRUI')  then "启锐/QIRUI"
        |when firstCategoryId = '10016' and (brandName_cn = '理光' or brandName_cn = 'RICOH')  then "理光/RICOH"
        |when firstCategoryId = '10016' and (upper(brandName) = '戴尔' or upper(brandName) = 'DELL') then '戴尔/DELL'
        |when firstCategoryId = '10016' and (upper(brandName) = '微星' or upper(brandName) = 'MSI') then '微星/MSI'
        |when firstCategoryId = '10016' and (upper(brandName) = '影驰' or upper(brandName) = 'GALAXY') then '影驰/GALAXY'
        |when firstCategoryId = '10016' and (upper(brandName) = '奇美' or upper(brandName) = 'LG') then '奇美/LG'
        |when firstCategoryId = '10016' and (upper(brandName) = '思科' or upper(brandName) = 'CISCO') then '思科/CISCO'
        |when firstCategoryId = '10016' and (upper(brandName) = '爱快' or upper(brandName)= 'IKUAI') then '爱快/IKUAI'
        |when firstCategoryId = '10016' and (upper(brandName) = '亿瓦' or upper(brandName)= 'EVEBOT') then '亿瓦/EVEBOT'
        |when firstCategoryId = '10021' and (upper(brandName) = '玛氏' or upper(brandName)= 'MARS') then '玛氏/MARS'
        |when firstCategoryId = '10021' and (upper(brandName) = '麦提莎' or upper(brandName) = 'MALTESERS') then '麦提莎/MALTESERS'
        |when brandName_cn rlike '其他' or brandName_cn rlike '其它' then '其他/OTHER'
        |
        |else brandName_cn end cn
        |from
        |brandplatdata
      """.stripMargin)
      .drop("brandName_cn")
      .withColumnRenamed("cn", "brandName_cn")
    case1.registerTempTable("table_yc_1")


    spark.sqlContext.sql(
      """
        |select *,
        |regexp_replace(brandName_cn,'[/]$','') cn
        |from
        |table_yc_1
      """.stripMargin)
      .drop("brandName_cn")
      .withColumnRenamed("cn","brandName_cn")
      .dropDuplicates("brandCcId")
      .registerTempTable("newTable_yc_1")

    val platformAllBrandDataAlls = spark.sqlContext.sql(
      """
        |select
        |*,
        |case when split(brandName_cn,"/")[1] is not null or brandName_cn rlike '/'
        |then concat(upper(split(brandName_cn,"/")[1]),"/",upper(split(brandName_cn,"/")[0]))
        |else upper(brandName_cn) end en
        |from
        |newTable_yc_1
      """.stripMargin)
//      .drop("brandName_en")
      .withColumnRenamed("en","brandName_en")
      .dropDuplicates("brandCcId")

    platformAllBrandDataAlls
  }
  /**
    * 该方法是去掉brandName_cn中的特殊字符例如国家，分类
    */
  def replace(data: String): String = {
    if (data.contains("眼镜") | data.contains("服饰") | data.contains("食品") | data.contains("用品") | data.contains("孕妇") | data.contains("棉品")
      | data.contains("宝宝")  | data.contains("乐器") | data.contains("除臭芳香用品")  | data.contains("国内") | data.contains("进口") | data.contains("原装") | data.contains("心钻装")
      | data.contains("铂金装") | data.contains("金装") | data.contains("银装") | data.contains("一级帮") | data.contains("新款") | data.contains("系列")
      | data.contains("营养辅食") | data.contains("护齿牙刷") | data.contains("手工") | data.contains("定制") | data.contains("工作室") | data.contains("牌")
      | data.contains("孕妈") | data.contains("纸尿裤") | data.contains("洗护") | data.contains("海淘") | data.contains("童装")
      | data.contains("内衣") | data.contains("品牌方") | data.contains("美国") | data.contains("运动户外") | data.contains("箱包") | data.contains("家电")
    ){
      data.toString.replaceAll("眼镜", "").replaceAll("服饰", "").replaceAll("食品", "")
        .replaceAll("乐器", "").replaceAll("除臭芳香用品", "").replaceAll("国内","").replaceAll("进口","").replaceAll("原装","").replaceAll("心钻装","").replaceAll("铂金装","")
        .replaceAll("金装", "").replaceAll("银装", "").replaceAll("一级帮","").replaceAll("新款","").replaceAll("系列","").replaceAll("心钻装","").replaceAll("营养辅食","")
        .replaceAll("护齿牙刷", "").replaceAll("手工", "").replaceAll("定制","").replaceAll("工作室","").replaceAll("牌","")
        .replaceAll("用品","").replaceAll("孕妇","").replaceAll("棉品","").replaceAll("宝宝","").replaceAll("孕妈","").replaceAll("纸尿裤","").replaceAll("洗护","")
        .replaceAll("海淘","").replaceAll("童装","").replaceAll("内衣","").replaceAll("品牌方","").replaceAll("美国","").replaceAll("运动户外","").replaceAll("箱包","")
    } else {
      data
    }
  }

}
